I need to select records from one table based on another (sounds a lot more simple than it is). I've got data like this:
Table 1 (Header) HeadGUID Status Name 1 1 Foo 2 1 Bar 3 1 Xyz 4 0 Zzz
Table 2 (Item) HeadGUID ItemGUID Status 1 10 1 2 20 1 2 30 0 3 40 0 4 50 0
I need my query to return the fields Header.HeadGUID & Header.Name, and it should only return 3 (Xyz). It's where all Items have a status of 0, for a given HeadGUID. I don't want any headers where Header.Status = 0, and I don't want any headers where there are ANY Items.Status = 1. I only want the records where Header.Status = 1 and all Items.Status = 0.
I can't figure out this "all items.status = 0" part. The closest I could come was returning records with "any items.status = 0", and this generates tons of data I don't need. I feel it should be possible with the joins, but I rarely use anything other than an inner join, so I'm unsure of the others. I did wonder if I should be solved with a union, but couldn't figure that out either
A little help please!
Edit - This is my best code:
select t1.HeaderGUID, t1.Name from Header t1 inner join Item t2 on t1.HeaderGUID = t2.HeaderGUID where t1.Status = 1 and t2.Status = 0
This is wrong though, as it also returns "Bar"
This post has been edited by chemicalfan: 10 January 2014 - 09:50 AM