Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mondeo
Constraint Violating Yak Guru
287 Posts |
 Posted - 2010-11-01 : 09:40:42
|
Hi,I have this simple query which returns 10000 rows.SELECT main.*FROM dbo.vwMain AS main I've added a simple LEFT join, but the results are not as expected. I want the same 10000 rows but with the price appended. However this query only returns 7000 rows. If a row doesn't exist in the pricing table I just want NULL to be returned for that field. How can I do this? ThanksSELECT main.*, prices.PR_BasicFROM dbo.vwMain AS main LEFT OUTER JOINPUB_CAR.dbo.NVDPrices AS prices ON main.ID = prices.PR_IdWHERE (prices.PR_EffectiveTo IS NULL) |
|
|
TimSman
Posting Yak Master
127 Posts |
Posted - 2010-11-01 : 09:58:44
|
| Drop the WHERE clause. |
 |
|
|
Mondeo
Constraint Violating Yak Guru
287 Posts |
Posted - 2010-11-01 : 10:02:37
|
| There are multiple rows with the same PR_ID in the prices table, the current one has PR_EffectiveTo set to NULL so I need to filter on this.Thanks |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-11-01 : 11:41:14
|
quote:
Originally posted by Mondeo
Hi,I have this simple query which returns 10000 rows.SELECT main.*FROM dbo.vwMain AS main I've added a simple LEFT join, but the results are not as expected. I want the same 10000 rows but with the price appended. However this query only returns 7000 rows. If a row doesn't exist in the pricing table I just want NULL to be returned for that field. How can I do this? ThanksSELECT main.*, prices.PR_BasicFROM dbo.vwMain AS main LEFT OUTER JOINPUB_CAR.dbo.NVDPrices AS prices ON main.ID = prices.PR_Id AND prices.PR_EffectiveTo IS NULLWHERE (prices.PR_EffectiveTo IS NULL)
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
JOIN problem