Impossible? Filter n:m - Relations, where 'n' has some Entries in 'm'? 2

[schittli]

Good afternoon

Maybe it's too late, but I have no idea where to start for
a simple(?) filter in a n:m relation:

I have an article-table.
To each article-record I can assign 0..n states.

The question: how can I get all Articles which have
State 1 AND State 2 assigned to it?

To see some Tables, assume the following data:

Table State : ID State
-------------------------
1 Open
2 Followup
3 Done

Table Article: ID Text
-----------------------
1 Text 1
2 Text 2
3 Text 3

Join-Table: ArticleID StateID
----------------------------------
1 1 (Open)
1 2 (Followup)

2 3 (Done)


Now, I need a Query to get all Articles, which have
State 1 (Open) AND State 2 (Followup) assigned.


Thanks in advance for and help!,
kind regards,
Thomas

 

[r937]

select A.ID
     , A.Text
  from Articles as A
inner
  join JoinTable as J
    on J.ArticleID = A.ID
inner
  join State as S
    on S.ID = J.StateID    
   and S.StateID in (1,2)
group
    by A.ID
     , A.Text
having count(*) = 2

r937.com | rudy.ca

 

[PHV]

A starting point:
SELECT ArticleID
FROM [Join-Table]
WHERE StateID In (1,2)
GROUP BY ArticleID
HAVING Count(*)=2

Another way:
SELECT ArticleID
FROM [Join-Table]
GROUP BY ArticleID
HAVING Min(StateID)=1 AND Max(StateID)=2

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

 

[schittli]

Perfect!... Thanks a lot for all of your fast answers and solutions!

I always wonder about SQL: if I see it, then it is logical, but it's hard to create one...

Thanks a lot again,
kind regards,
thomas