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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Excluding a parent record with multiple child recs

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 table
Job Items = Child table

Where 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]
SELECT
j.job_no,
j.job_start_date
FROM
job j
INNER JOIN JobItem ji
ON ji.job_no = j.job_no
GROUP BY j.job_no,
j.job_start_date
HAVING SUM(CASE WHEN ji.job_item_code = 'Pound' THEN 1 ELSE 0 END) = 0
[/code]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-06-17 : 11:27:39
Thanks Visakh!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-17 : 11:53:18
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -