SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Excluding a parent record with multiple child recs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Maverick_
Posting Yak Master

107 Posts

Posted - 06/17/2013 :  06:00:28  Show Profile  Reply with Quote
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

Edited by - Maverick_ on 06/17/2013 11:28:11

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/17/2013 :  06:05:28  Show Profile  Reply with Quote

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



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

Maverick_
Posting Yak Master

107 Posts

Posted - 06/17/2013 :  11:27:39  Show Profile  Reply with Quote
Thanks Visakh!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/17/2013 :  11:53:18  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000