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 |
Maverick_
Posting Yak Master
107 Posts |
Posted - 2013-06-17 : 06:00:28
|
Hi all,I am trying to come up with a way of excluding a parent record when its related child table has multiple records.For example take:Job = parent tableJob Items = Child tableWhere a Job has a Pound item in its job item table, I want that job to be excluded from results.The problem is a Job could have multiple items including a Pound item.E.g. Job 123456 has a Pound, ADHOC, HOURLY job items.I want to the SQL to exclude where it finds the Pound item if its a single item, or part of multiple items for a Job.Is there a way to do this? One way I was thinking it could work is using sub-select.What do you think? Any suggestions welcome |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 06:05:28
|
[code]SELECTj.job_no,j.job_start_dateFROMjob jINNER JOIN JobItem jiON ji.job_no = j.job_noGROUP BY j.job_no,j.job_start_dateHAVING SUM(CASE WHEN ji.job_item_code = 'Pound' THEN 1 ELSE 0 END) = 0[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Maverick_
Posting Yak Master
107 Posts |
Posted - 2013-06-17 : 11:27:39
|
Thanks Visakh! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 11:53:18
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|