Query Question

Question

Query Question

David Phillips-317533

Valued Member

Points: 62
More actions
November 13, 2006 at 2:37 pm

#94820

I have two tables set up as a one to many relationship. How do I list all the fields in the MAIN table (one) based on whether all the records in the SUB table (many) have been closed?

Lets say SUB has 20 records for ‘MainKey’ 10 with all records having the field ‘Closed’ set to 1 but SUB has 32 records for ‘MainKey’ 5 but only 15 records in Sub have the ‘Closed’ field set to 1? I only want to list the MAIN table information for ‘MainKey’ 10. How do I do this?

Note: MAIN has many records so I’ll need to loop through them all to determine if all the SUB records have been closed.

Thanks,
Dave

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

This list all the records where ALL sub records are closed... chose change to WHERE EXISTS to change the behavior!

Select MainID, Col1, Col2 FROM dbo.Main WHERE NOT EXISTS (SELECT * from dbo.SUB WHERE Sub.MainID = Main.MainID and SUB.Closed = 0 /*false*/)

David Phillips-317533 Valued Member Points: 62 More actions · Answer 2

Ninja,

Althought your solution is close it still does not quit answe rthe question. Using your method it will return the key from the main table if only one of the many records in trhe sub table has closed set to 1. I want it to return only the key from the main table when ALL records in the sub tabel are closed.

I'll try and work with what you sent to see if I can figure something else, it's close but still not quite there.

Thanks,

Dave

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Maybe if we worked on the same data it would be easier. Here's what I have. Tell me if it works for you and the problems you encounter, if any :

Declare @Main table (MainID int NOT NULL PRIMARY KEY CLUSTERED)

Declare @Sub table (MainID int NOT NULL, Closed BIT NOT NULL DEFAULT(0))

--ALL OPENED

INSERT INTO @Main (MainID) VALUES (1)

INSERT INTO @Sub (MainID, Closed)

SELECT 1, 0

UNION ALL

SELECT 1, 0

--SOME CLOSED AND SOME OPENED

INSERT INTO @Main (MainID) VALUES (2)

INSERT INTO @Sub (MainID, Closed)

SELECT 2, 0

UNION ALL

SELECT 2, 1

UNION ALL

SELECT 2, 1

UNION ALL

SELECT 2, 0

--ALL CLOSED

INSERT INTO @Main (MainID) VALUES (3)

INSERT INTO @Sub (MainID, Closed)

SELECT 3, 1

UNION ALL

SELECT 3, 1

SELECT Main.MainID FROM @Main Main WHERE NOT EXISTS (SELECT * FROM @Sub Sub WHERE Sub.MainID = Main.MainID AND Sub.Closed = 0)

--1 row(s) affected

--MainID

--3

David Phillips-317533 Valued Member Points: 62 More actions · Answer 4

Sorry for the delay in responding.

Thanks for your help and suggestions, here's the query I ended up:

SELECT

s.Key

FROM Sub s inner join Main m on m.key = s.key

WHERE s.key IN

(Select Count(*) as Counter from Sub where Closed = 0 group by key)

This returns the records I was looking for.

Thanks again - Dave

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Thanx for the feedback, but don't you mean this :

SELECT

s.Key

FROM Sub s inner join Main m on m.key = s.key

WHERE s.key IN

(Select Key from Sub where Closed = 0 group by key having count(*) = 0)

Also my version is most likely faster to execute than yours. Compare the 2 executions plans and see for yourself!

David Phillips-317533 Valued Member Points: 62 More actions · Answer 6

I'll give it a try and let you know.

Thanks

*************************

I tried it but had to modify your solution just slightly to return only the records where all subs were closed. To be honest I'm not sure why it's working this way but it is.

I'm not sure what I'm looking at on the execution plan. Your Sort was 1% higher than mine but the rest of your numbers where all 1% lower. Not sure of the significance of all this though.

Both are still pretty quick. I might try and populate the the tables with 1000's of records and re-run, I'm sure there will be major differences then with what I would guess, yours being faster.

s.Key

FROM Sub s inner join Main m on m.key = s.key

WHERE s.key not IN

(Select Key from Sub where Closed = 0 group by key having count(*) > 0)

Again I'll let you know.