February 27, 2013 at 6:40 am
Good day Gents,
I have been trying on how to do this but i seems cant to think well of how to get this right.
I have two tables, A and B
Table A has two records as follow
IDA
1
2
Table B has lets say four records where IDA is a unique ID from table A
IDA - Status - IDB
1 - Closed - 10
1 - Closed - 11
2 - Active - 12
2 - Closed - 13
Now i want to select all records from B where their Status is Closed, this can only be if all records are closed. i.e the query should return
IDA - Status - IDB
1 - Closed - 10
1 - Closed - 11
The query could be something like this.
Select a.IDA, b.Status, b.IDB
from A a
JOIN B b on a.IDA = b.IDB
February 27, 2013 at 6:56 am
I'd probably go with something like this :
Select a.IDA, b.Status, b.IDB
from tblA a INNER JOIN tblB b on a.IDA = b.IDB
AND b.status = 'closed'
WHERE NOT EXISTS
(
SELECT 1 FROM tblB AS b2 WHERE STATUS <> 'closed' AND b2.idb = A.ida
)
February 27, 2013 at 7:00 am
Hi,
Try:
with CTE_B as
(
select IDA from B
group by IDA
having count(Status) = count(case when Status = 'Closed' then Status end)
)
select c.IDA, b.Status, b.IDB
from CTE_B as c
join B as b on b.IDA = c.IDA
Hope this helps.
February 27, 2013 at 7:05 am
One way to achieve this
SELECT*
FROMB AS B
WHERE Status = 'Closed'
ANDNOT EXISTS( SELECT * FROM B AS B1 WHERE B.IDA = B1.IDA AND B1.IDA <> 'Closed' )
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 27, 2013 at 7:12 am
Hi guys
Thank you so much for the prompt help, I’m sorted out now.
Kingston Dhasian - the easiest way: Took 00:02:10 to execute
imex – I’m not really good with CTE, I still need to read up more on it: Took 00:00:04 to execute
sven.treffinger – very good and simply: Took 00:01:23 to execute
😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply