showing all data rows when joining tables

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

0	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

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

0	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

0	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

Latest Posts