February 15, 2011 at 11:57 pm
Hi all, I want to check if my Inner Joins are best practice.
I have 4 tables. One table is referred to by TWO of the other tables.
I used to write this code:
Select * from product P, owner O, manufacturer M, location L
where P.ownerid = O.ownerid
and P.manid = M.manid
and M.locid = L.locid
and P.locid = L.locid
As you can see two tables both refer to the location table
If I write this using Innder joins. I seem to have to create TWO aliases for the Location table.
Is this the CORRECT / BEST way?
Select *
from product P,
inner join owner O ON P.ownerid = O.ownerid
inner join manufacturer M ON P.manid = M.manid
inner join location L1 ON M.locid = L1.locid
inner join location L2 ON O.locid = L2.locid
Any comments or suggestion muchly appreciated.
Cheers
Budz
The code for the schema / table design is below:
create table location (
locid integer primary key,
locname varchar2(30) );
create table manufacturer (
manid integer primary key,
manname varchar2(30),
locid integer,
foreign key (locid ) references location );
create table owner (
ownerid integer primary key,
ownername varchar2(30),
locid integer,
foreign key (locid ) references location );
create table product (
prodid integer primary key,
prodname varchar2(30),
ownerid integer,
manid integer,
foreign key (manid ) references manufacturer,
foreign key (ownerid) references owner );
February 16, 2011 at 1:12 am
The join in where clause is an older style and is not much in favour any longer.
Regardless of which way you do it, you need to list the location table twice and alias it twice. The form you have in the first query will only return data if M.locid = P.locid.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply