March 12, 2008 at 10:20 am
Hi,
I am debugging a procedure that has multiple joins. The problem i find is one table doesnt have a value that is needed to display the required result. I cannot change the joins coz data comes from that table only.
How would i resolve this issue? In the code below, columns b2 and c4 from table2 and Table3 match but the column a1 from TableA doesnt match and i found that it did not exist.
How to interchange the joins so that the result set includes the column b2 and c4?
Eg:
Select * from Table1 A
Join Table2 B on
A.a1 = b.b2
left join on Table 3 C
on
A.a1 = C.c4
March 12, 2008 at 10:30 am
The order of the join isn't going to fix "missing" data. If tables 2 and 3 don't have data matching column a1 in Table1, then there will be rows with null values in those columns.
I get the feeling you're not asking the question you really want. What (specifically) is the issue you're running into? An example would be nice.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 12, 2008 at 10:38 am
oops !! sorry fi it was too confusing.. Am confused as well :p
Basically all 3 tables undergo a transaction process. So eventually "one data" is not entered in Table1, but Table2 and Table3 have it entered by users. The join is required to produce a report. I kind fo figured out that a right outer join would work. But i eventually get additional rows which i need to eliminate.
Select * from Table1 A
right outer Join Table2 B on
A.a1 = b.b2
right outer join on Table 3 C
on
A.a1 = C.c4
March 12, 2008 at 10:45 am
The usual solution to that problem is to put "is not null" statements in the Where clause of the query, if I'm understanding your question correctly.
- 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
March 12, 2008 at 2:24 pm
Try this:
Select * from
(Table2 B
left join on Table 3 C on B.b2 = C.c4)
Left Join Table1 A on A.a1 = b.b2
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply