Joins

marthaj · June 24th, 2003, 07:19 AM

Hi folks,
  I need some help in ocmstructing a join.
I have three tables. The structure is:

Tables (columns listed underneath table name):
1.SoldProducts
     Area
     ProductId
     SalesmandId

2.Salesman
     SalesmanId
     Name
     BranchPk

3.Branch
     BranchPk
     BranchId
     BranchName

What I need to do is select from the table SoldProducts where the area equals some value and where the BranchId equals some value.
This what I have so far:

   Select SoldProducts.Area,SoldProducts.ProductId,
          Salesman.Name,Branch.BranchId,Branch.Name
            From SoldProducts
                inner join Salesman on Salesman.SalesmanId = SoldProducts.SalesmanId
              join Salesman on Branch.BranchPk = Salesman.BranchPk
               where SoldProducts.Area = some value
               And Branch.BranchPk = some value

     heavens knows this isn't right. What do i need tod o and understand to be able to construct this properly. I can not change the strucure of the tables. Thank you so very much
marthaj

Jeff Mason · June 24th, 2003, 07:30 AM

You're close. Try:

Code:

SELECT SoldProducts.Area,SoldProducts.ProductId,Salesman.Name,Branch.BranchId,Branch.Name
   FROM SoldProducts
    INNER JOIN Salesman ON SoldProducts.SalesmanID=Salesman.SalesmanID
    INNER JOIN Branch ON Salesman.BranchPk=Branch.BranchPk
WHERE SoldProducts.Area = @areavalue
  AND Branch.BranchPk = @Branchvalue;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

nbryson · June 24th, 2003, 07:31 AM

Hi

I hope this helps....

Select SoldProducts.Area,SoldProducts.ProductId,
Salesman.Name,Branch.BranchId,Branch.Name
From SoldProducts sp inner join
Salesman s on sp.SalesmanId = s.SalesmanId inner join
Branch b on b.BranchPk = s.BranchPk
where SoldProducts.Area = some value
And Branch.BranchPk = some value

Cheers

Nickie

marthaj · June 25th, 2003, 09:33 AM

Thank you all for replying.
Would the following be correct too ??

Select a.Area,a.ProductId,
b.Name,b.BranchId,c.Name
From SoldProducts
inner join Salesman as a on b.SalesmanId = a.SalesmanId
inner join Branch as c on c.BranchPk = b.BranchPk
where a.Area = some value
And c.BranchPk = some value

Jeff Mason · June 25th, 2003, 10:30 AM

Quote:

quote:Would the following be correct too ??

Select a.Area,a.ProductId,
b.Name,b.BranchId,c.Name
From SoldProducts
inner join Salesman as a on b.SalesmanId = a.SalesmanId
inner join Branch as c on c.BranchPk = b.BranchPk
where a.Area = some value
And c.BranchPk = some value

No.

Your aliases are confused. An alias is simply a name you give to a table mentioned in the FROM clause. If you give a table an alias in the FROM clause, you must then refer to columns in that table using that table alias as a prefix to the column name everywhere else in the query. You do not need to prefix the table name or alias to a column if the column name can be unambiguously determined from all the tables in the FROM clause.

It is helpful to use mnemonic alias names for your tables so it is easier to understand which table a column expression is referring to. Using an arbitrary 'a', 'b', and 'c' makes your query very hard to read and understand.

Note that the 'as' keyword is optional when defining an alias. I generally do not use it, as I think it tends to clutter up the query.

I would write your query using aliases as:

Code:

SELECT p.Area, p.ProductId, s.Name, b.BranchId, b.BranchName
   FROM SoldProducts p 
      INNER JOIN Salesman s ON p.SalesmanId = s.SalesmanId
      INNER JOIN Branch b ON s.BranchPk = b.BranchPk
   WHERE p.Area = some value
     AND b.BranchPk = some value

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

marthaj · June 26th, 2003, 07:41 AM

Thank you kindly for your response and for the advise and tips.
I see what you are saying about confusion and the alias for table name.It makes better sense.
The one thing that also consfuses me, is which qualifier is used first on the equal sign. Such as

INNER JOIN Branch b ON s.BranchPk = b.BranchPk

What rule can I hold in my mind to prevent errors ?? I don't know if I am reading too much or thinking too little and confusing myself.
Again, I thank you for the time you have given.

12th_Man · June 26th, 2003, 08:17 AM

The order in which you specify the ON portion of the join clause does not matter, i.e. "INNER JOIN Branch b ON s.BranchPk = b. BranchPk" is the same as "INNER JOIN Branch b ON b.BranchPk = s.BranchPk (at least for MSSQL, I don't know about other DBMS). Also, for an inner join or full outer join the order in which you specify the tables doesn't matter. Of course if you use a left or right join, the order of the tables would matter, but the ON portion would not.

-Mike

Jeff Mason · June 26th, 2003, 09:02 AM

As the previous poster has said, it doesn't matter; 'ON a=b' is exactly equivalent to 'ON b=a'. I like to put the order of testing in the same order as the tables mentioned in the FROM clause, e.g.:

Code:

    ...
    FROM Table1 INNER JOIN Table2
        ON Table1.col=Table2.col
    ...

...but this purely a matter of taste, I think. The optimizer will freely rearrange the order of evaluation of the expressions to suit its query plan anyway, so the order is only for your benefit.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com