Sub Query Question

[jadams0173]

I have a table with this structure

OrderID Complete
1234 yes
1234 yes
1234 no
5678 no
9123 yes
9123 yes

What I am trying to get back is the OrderID where all the Complete are yes. So from the above my results would be.

OrderID Complete
9123 yes

I'm kinda close but the order ID's like 5678 where there are only one entry still return even though there is a NO in complete column. I've tried modifing the query below to add complete=yes in the subquery but I got an error that said something to the effect of I needed to use the EXISTS reserve work in the main query From clause.

my query so far looks like:

SELECT OrderID, Complete
FROM tblData
GROUP BY OrderID, PicklistComplete
HAVING OrderID In (SELECT [OrderID] FROM [tblData] As Tmp GROUP BY [OrderID],[picklistcomplete] HAVING Count([orderID])=1))

 

[Melagan]

SELECT d.OrderID
, d.Complete

FROM tblData AS d

WHERE d.OrderID Not In (SELECT OrderID FROM Data WHERE Complete = "no";)

GROUP BY d.OrderID
, d.Complete;

Should do =)


~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

One way:
SELECT DISTINCT OrderID, PicklistComplete
FROM tblData
WHERE PicklistComplete = True
AND OrderID Not In (SELECT OrderID FROM tblData WHERE PicklistComplete = False)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

Thanks to the both of you!!!! I have tried each of you solutions and they return a different ammount of records on the same data. I'm going to sift through the results and see what I come up with.

Thanks again for the help. I've gotta get a SQL book..a big THICK one.

 

[Melagan]

My SQL groups the data so you only see one record for each OrderID that meets the criteria; you should see fewer records in mine for that reason. If you were to revise my SQL as such:

SELECT d.OrderID
, d.PickListComplete
, Count(d.OrderID) as CountofID

FROM tblData AS d

WHERE d.OrderID Not In (SELECT OrderID FROM Data WHERE PickListComplete = "no";)

GROUP BY d.OrderID
, d.Complete;

Both SQL should technically return the same amount of records, as you'll see when you can actually see the count.



~Melagan
______
"It's never too late to become what you might have been.