December 7, 2006 at 7:03 am
I am trying to get one record from the issue_main table when all related log rows are at a status of 3 and the issue_main status is 3.
This partially works, but if a log has two rows for which one of them has a status of 3 then it is returning the issue main record (which i can understand), but how would i alter this to achieve the above?
Many thanks in advance...
SELECT distinct dbo.ISSUE_MAIN.ISSUE_MAIN_ID, dbo.ISSUE_MAIN.ISSUE_REF
FROM dbo.ISSUE_MAIN
WHERE 3 IN (select dbo.issue_log.issue_log_status_id from issue_log
where dbo.ISSUE_LOG.ISSUE_main_ID = dbo.issue_main.issue_main_id and issue_log.issue_log_status_id = '3') AND (dbo.ISSUE_MAIN.ISSUE_MAIN_STATUS_ID = '3')
December 7, 2006 at 7:46 am
SELECT distinct im.ISSUE_MAIN_ID, im.ISSUE_REF
FROM dbo.ISSUE_MAIN As im
Inner Join
-- Join derived table that returns rows where all in the group are status 3
(
Select
issue_log_status_id
From issue_log
Group By issue_log_status_id
Having Sum(Case When issue_log_status = '3' Then 1 Else 0 End) = Count(*)
) dt
On (dt.issue_log_status_id = im.issue_log_status_id)
December 7, 2006 at 8:32 am
Thanks for that, but it is returning the exact same records as my initial query i posted?
Here is the slightly altered version of your posted sql statement to fit my example. I can see what your trying to get it to do but no difference - any ideas - thanks again....
SELECT distinct im.ISSUE_MAIN_ID, im.ISSUE_REF
FROM dbo.ISSUE_MAIN As im
Inner Join
-- Join derived table that returns rows where all in the group are status 3
(
Select
issue_log_status_id, issue_main_id
From issue_log
Group By issue_log_status_id, issue_main_id
Having Sum(Case When issue_log_status_ID = '3' Then 1 Else 0 End) = Count(*)
) dt
On (dt.issue_main_id = im.issue_MAIN_id)
WHERE im.ISSUE_MAIN_STATUS_ID = '3'
December 7, 2006 at 8:47 am
Sorry, under-caffeinated moment. Didn't clue in that issue_main_id was the joining column. Fix it by grouping on just issue_main_id in the derived table.
SELECT distinct im.ISSUE_MAIN_ID, im.ISSUE_REF
FROM dbo.ISSUE_MAIN As im
Inner Join
-- Join derived table that returns rows where all in the group are status 3
(
Select issue_main_id
From issue_log
Group By issue_main_id
Having Sum(Case When issue_log_status_ID = '3' Then 1 Else 0 End) = Count(*)
) dt
On (dt.issue_main_id = im.issue_main_id)
WHERE im.ISSUE_MAIN_STATUS_ID = '3'
December 7, 2006 at 8:52 am
Your a star!
Should have spotted that one myself...
Anyway, thanks again and get yourself a thoroughly deserved coffee!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply