June 18, 2009 at 1:52 pm
I get the same results from both types of joins. However for me one type is MUCH easier than the other.
But!!! Which is the best to use or does it really matter???
Join 1
FROM
(
(
tbl_Whip_Type
RIGHT JOIN
(
tbl_Wire_Type
RIGHT JOIN
tbl_Whip
ON
tbl_Wire_Type.Wire_Type = tbl_Whip.Wire_Type
)
ON
tbl_Whip_Type.Whip_Type = tbl_Whip.Whip_Type
)
RIGHT JOIN
tbl_Assembly
ON
tbl_Whip.Assembly_ID = tbl_Assembly.Assembly_ID
)
RIGHT JOIN
tbl_Drawing
ON
tbl_Assembly.Drawing_ID = tbl_Drawing.Drawing_ID
Join 2
FROM
tbl_Whip_Type
RIGHT JOIN
(
tbl_Wire_Type
RIGHT JOIN
(
tbl_Whip
RIGHT JOIN
(
tbl_Assembly
RIGHT JOIN
tbl_Drawing
ON
tbl_Assembly.Drawing_ID = tbl_Drawing.Drawing_ID
)
ON
tbl_Whip.Assembly_ID = tbl_Assembly.Assembly_ID
)
ON
tbl_Wire_Type.Wire_Type = tbl_Whip.Wire_Type
)
ON
tbl_Whip_Type.Whip_Type = tbl_Whip.Whip_Type
I would really appreciate your input.
June 18, 2009 at 2:02 pm
I would need to see table definitions to be able to say anything about either one.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 18, 2009 at 2:08 pm
If you are asking about Keys.
The keys are:
tbl_Drawing: The keys are not used in the join - Company, Division, Drawing, Revision, Area;
- Drawing_ID is not a key but is used in the join
tbl_Assembly: Drawing_ID & Detail_No
- Assembly_ID is not a key but is used in the join
tbl_Whip: Whip_ID
- Assembly_ID is not a key but is used in the join
- Whip_Type is not a key but is used in the join
- Wire_Type is not a key but is used in the join
tbl_Whip_Type: Whip_Type
tbl_Wire_Type: Wire_Type
Does that help???
Many of the indexes are not used as part of the join. This is a two year old db and not layed out as well as it should be. I need to modify it ASAP and perhaps fix it later.
June 18, 2009 at 2:24 pm
I suspect it's all the same thing just moving the parenthesis around. But, having all those parenthesis in there makes it much harder to read. I don’t have too much of an issue with RIGHT joins, but it seems more common to get outer joins up as LEFT joins if possible.FROM
tbl_Drawing
LEFT OUTER JOIN
tbl_Assembly
ON tbl_Assembly.Drawing_ID = tbl_Drawing.Drawing_ID
LEFT OUTER JOIN
tbl_Whip
ON tbl_Whip.Assembly_ID = tbl_Assembly.Assembly_ID
LEFT OUTER JOIN
tbl_Wire_Type
ON tbl_Wire_Type.Wire_Type = tbl_Whip.Wire_Type
LEFT OUTER JOIN
tbl_Whip_Type
ON ON tbl_Whip_Type.Whip_Type = tbl_Whip.Whip_Type
June 18, 2009 at 2:33 pm
Okay...
That looks very clean. I need to do some reading up on the difference between a Left Join and a Left Outer Join.
June 18, 2009 at 2:42 pm
Left and Left Outer are the same thing. OUTER is optional.
June 18, 2009 at 2:47 pm
Many thanks...
Putting the On ... right after each table is MUCH easier that the way I was doing it.
Much easier to follow, etc...
Thank you again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply