showing all data rows when joining tables

Posted by Kng under Sql Server on 1/24/2011 | Points: 10 | Views : 1596 | Status : [Member] | Replies : 6
Hi,

I am trying to join 2 tables, however I want every data row in table 1 to be shown even if table 2 does not have the corresponding data.

eg.
select a.name, b.price, c.units from #a
join #b with (nolock) on b.id=a.id
where b.date = '12/31/2010'
group by name, price, units
order by units

in this query, if there are 20 entries in a, and 16 corresponding entries in b, only 16 entries will be shown. I am trying to make it show all 20 entries even if the data in a can't be joined to b.

Thanks,
Keith




Responses

Posted by: SheoNarayan on: 1/24/2011 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Did you try LEFT Join?,

Read this http://www.mssqltips.com/tip.asp?tip=1667 that will help you understanding the JOINS in a better ways!

Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Kng, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kng on: 1/24/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Sheo,

Thanks for the prompt response. I tried the different joins but still wouldnt work. Theoretically, left join should work. i identified the issue is on "where b.date = '12/31/2010'". There is no data in table b for date 12/31/2010, as such, causing the data not to show entirely.

Any insights into this?

Keith


Kng, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kng on: 1/24/2011 [Member] Starter | Points: 25

Up
0
Down
I think I can provide more details to something i am looking for:

If there is no date from 12/31/2010 available, i still want the data row to be shown.

Ideally, if there is no date from 12/31/2010 available, look towards, 11/30/2010 or 10/31/2010 and so on.
If not, show NULL.

Keith

Kng, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: SheoNarayan on: 1/24/2011 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Thanks Kng,

In your query you have used WHERE with = clause that will surely filter to only those records that matches the condition. There is something called COALESCE function that may help (http://www.dotnetfunda.com/interview/exam1664-what-is-the-use-of-coalesce-function-in-sql-server-.aspx ). I am not good in SQL Server however looks like COALESCE should solve your problem.

Thanks!

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Kng, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kng on: 1/24/2011 [Member] Starter | Points: 25

Up
0
Down
Thanks, Sheo. Coaslesce is outstanding!

Kng, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 1/24/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Kng

Can you try this...?

select a.name, b.price, b.units from a(nolock) 

left join b(nolock) on (b.id=a.id and b.[Date] = '12/31/2010')
group by name, price, units
order by units
Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Kng, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response