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 |
|
CodeNinja
Starting Member
2 Posts |
 Posted - 2014-06-21 : 08:08:59
|
Hello, I am trying to figure out why my select statement is not providing at least 1 record for each item in my RFWData table. In this case, in theory, it should provide 1 record for each matching record in RFWData. Matching records being those with Brand = 'CY' and wave = '2014-JAN-JUN'. The tables ItemCompliance and Comments may or may not have a matching record, but they will never have more than 1 matching record for a Brand, Wave, and Region. I would greatly appreciate any help as to what I am doing wrong... I am wondering if I need some nested query to get what I want, or if I am missing something simple... Here is my from and where clauses...FROM ( (RFWData AS r LEFT JOIN ItemCompliance AS i ON (r.Wave = i.[Wave - Audit Form]) AND (r.Brand = i.Brand) AND (r.UniqueID = i.[Question Reference]) )LEFT JOIN ItemCompliance AS ip ON (r.PrevWave = ip.[Wave - Audit Form]) AND (r.Brand = ip.Brand) AND (r.UniqueID = ip.[Question Reference])) LEFT JOIN Comments AS c ON (r.Wave = c.Wave) AND (r.Brand = c.Brand) AND (r.UniqueID = c.QuestionReference) WHERE (r.brand = 'CY' ) AND (r.wave = '2014-JAN-JUN' or r.wave is null) AND (i.region = 'Overall' or i.region is null) and (ip.region = 'Overall' or ip.region is null) and (c.continent = 'Overall' or c.continent is null); |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-06-21 : 09:20:15
|
[code]FROM dbo.RFWData AS rLEFT JOIN dbo.ItemCompliance AS i ON i.[Wave - Audit Form] = r.Wave AND i.Brand = r.Brand AND i.[Question Reference] = r.UniqueID AND i.region = 'Overall'LEFT JOIN dbo.ItemCompliance AS ip ON ip.[Wave - Audit Form] = r.PrevWave AND ip.Brand = r.Brand AND ip.[Question Reference] = r.UniqueID AND ip.region = 'Overall'LEFT JOIN dbo.Comments AS c ON c.Wave = r.Wave AND c.Brand = r.Brand AND c.QuestionReference = r.UniqueID AND c.continent = 'Overall'WHERE r.brand = 'CY' AND (r.wave = '2014-JAN-JUN' OR r.wave is null);[/code]
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
CodeNinja
Starting Member
2 Posts |
Posted - 2014-06-22 : 23:31:30
|
| You guys rock!!! I have been playing with this for a few days, and could not understand why I was getting less results than expected. Thank you so much Visakh16 for explaining WHY it would not work, and Thank you so much swePeso for showing me an example of how to make it work. I consider myself a decent amature programmer, and hope to be a true professional some day... I believe you have helped me take 1 step in that direction.CN. |
|
|
|
|
|
|
|
|
Help with From and Where Clause