April 21, 2004 at 2:36 am
Hello to all you SQL Gurus 🙂
I'm running around in circles and can't work out what the right approach would be...
I'm trying to find all records that have IACID = 1 and 2 and 5
and where the IExpID is greater or equal 12.
The query below doesn't cut it, I'm thinking a nested query might do the trick but I'm at a loss on how to go about it.
your help is truely appreciated.
Thanks
Wirri
Table X
ID IACID IExpID
1 1 18
1 2 12
1 5 12
2 5 12
2 1 12
2 2 12
3 1 14
3 2 18
4 1 12
5 2 12
6 5 11
This doesn't work 🙁
Select
ID
FROM Table X
Where
(IACID = 1
AND
IExpID >=12)
AND
(IACID = 2
AND
IExpID >=12)
AND
(IACID = 5
AND
IExpID >=12)
April 21, 2004 at 3:02 am
Select ID
FROM Table
Where IACID IN (1,2,5)
AND IExpID >=12
April 21, 2004 at 3:17 am
The only way i can see of doing this is to alias the table and self join the tables.
Select
ID
FROM Table a INNER JOIN Table b on a.ID = b.ID
INNER JOIN Table c ON a.ID = c.ID
WHERE a.IACD = 1 AND b.IACD = 2 and c.IACD = 5 and
a.IExpID >=12 and b.IExpID >=12 and c.IExpID >=12
Dave
April 21, 2004 at 3:27 am
Misunderstood original Q...try below
Select ID
FROM Table
Where IACID IN (1,2,5)
AND IExpID >=12
GROUP BY ID
HAVING COUNT(*) = 3
April 22, 2004 at 6:45 am
Hmm.. the question does not state whether combination of ID+IACID must be unique. If it isn't unique, then AH's solution won't work... but just a simple modification of the last line should help :
Select ID
FROM Table
Where IACID IN (1,2,5)
AND IExpID >=12
GROUP BY ID
HAVING COUNT(DISTINCT IACID) = 3
HTH, Vladan
April 22, 2004 at 6:58 am
This depends on what columns form the primary key (all have "ID" in so this is ambiguous).
If all 3 columns form the primary key, then AH's won't necessarily work because you could have records like this:
ID IACID IExpID
1 1 18
1 2 12
1 2 15
Which would also return results even in the absence of a IACID = 5 row. In this scenario I would use Dave's (although change select ID to select a.ID to avoid an ambiguous column name error).
However, if the primary key is (ID,IACID) then both will work.
April 22, 2004 at 7:17 pm
This query does the trick for me, since it allows me to
search for different iexpid values as well.
select id from x
where
(iacid = 1 and iexpid>=12)
or
(iacid = 2 and iexpid >=12)
or
(iacid = 5 and iexpid >= 12)
group by id
having count(*) = 3
Thanks for all your replies
Wirri
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply