May 25, 2006 at 1:57 pm
I am trying to join 3 tables. the main table should have 10 records for the selected provider, the second table should have 1 record and the third table should have 7 records.
I want to show all 10 records regardless of the number of records in the third table. The following is a sample of the join I am using.
FROM COMPARE a
join Provider1 b on a.PROVNO1 = b.providerno
JOIN provcom c on a.PROVNO1 = c.providerno and a.FYE = c.dteFiscalYearEnd
I wnat to receive a record for each in table a even if there are no matches in c.
I have tried to do this without the a.FYE = c.dteFiscalYearEnd but when I run it without it gives me more records than I want. I get 70 records.
7 records for each record in table a.
May 25, 2006 at 2:00 pm
LEFT OUTER JOIN provcom c on a.PROVNO1 = c.providerno and a.FYE = c.dteFiscalYearEnd
May 25, 2006 at 2:03 pm
I tried that and i still receive only the records that match.
May 25, 2006 at 2:13 pm
I found my problem. It was in the WHERE clause. I fixed it and it is OK now.
May 26, 2006 at 10:35 am
Just a side note. If you do something like this :
Select * from A Left Join B ON A.id = B.id
where B.AnyCol = 'Anything'
You essentially turned this statement back into an inner join. That is because when no match is found, a null is returned for the column and NULL = 'Anything' does not return TRUE (and does not return false either but that's another story).
May 26, 2006 at 10:36 am
... and if you were in need to use that where condition on the B table, you'd have to put that condition in the join like so :
ON A.id = B.id AND B.AnyCol = 'Anything'
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply