SQL Query help (AND across rows)
Hi all,
Need some help with this.
3 tables - Tag_Table, Media_Table, TagXMedia_Table
TagID is primary key of Tag_Table and foreign key in TagXMedia_Table
MediaID is a primary key of MediaTable and a foreign key of TagXMedia_Table
So i can have multiple tags reference multiple media items.
What i want to be able to do is select all the mediaId's from the TagXMedia_Table which have a TagId of x AND x AND x etc
So I am trying to select single MediaID's which have all of the given TagIds - like an AND select statement but accross the rows. I cant do the simple select * from TagXMedia_Table where TagID=1 AND TagID=2 for example as i have actually got:
TagID | MediaID
----------------------
1 | 40
1 | 59
2 | 40
and i want to return 40 in this case.
Hope this makes some sense.
Need some help with this.
3 tables - Tag_Table, Media_Table, TagXMedia_Table
TagID is primary key of Tag_Table and foreign key in TagXMedia_Table
MediaID is a primary key of MediaTable and a foreign key of TagXMedia_Table
So i can have multiple tags reference multiple media items.
What i want to be able to do is select all the mediaId's from the TagXMedia_Table which have a TagId of x AND x AND x etc
So I am trying to select single MediaID's which have all of the given TagIds - like an AND select statement but accross the rows. I cant do the simple select * from TagXMedia_Table where TagID=1 AND TagID=2 for example as i have actually got:
TagID | MediaID
----------------------
1 | 40
1 | 59
2 | 40
and i want to return 40 in this case.
Hope this makes some sense.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
no, only access is a bit stressful on putting brackets everywhere ...
select t1.mediaid
from TagXMedia_Table t1
join TagXMedia_Table t2
on ( t1.mediaid = t2.mediaid
and t1.tagid = 1
and t2.tagid = 2 )
select t1.mediaid
from TagXMedia_Table t1
join TagXMedia_Table t2
on ( t1.mediaid = t2.mediaid
and t1.tagid = 1
and t2.tagid = 2 )
ASKER
ok i think i got it - thanks (tables names slighty changed)
SELECT t1.MediaId
FROM RefTable AS t1 INNER JOIN RefTable AS t2 ON t1.MediaId = t2.MediaId
WHERE (((t1.TagId)=1) AND ((t2.TagId)=2));
SELECT t1.MediaId
FROM RefTable AS t1 INNER JOIN RefTable AS t2 ON t1.MediaId = t2.MediaId
WHERE (((t1.TagId)=1) AND ((t2.TagId)=2));
ASKER
ah thanks - i had already added the WHERE which seemed to make it work although so did the brackets:
SELECT t1.MediaId
FROM RefTable AS t1 INNER JOIN RefTable AS t2 ON t1.MediaId = t2.MediaId AND t1.TagId=1 AND t2.TagId=2;
thankyou
SELECT t1.MediaId
FROM RefTable AS t1 INNER JOIN RefTable AS t2 ON t1.MediaId = t2.MediaId AND t1.TagId=1 AND t2.TagId=2;
thankyou
ASKER
I am giving this a try in access but i get an error on 't1.tagid ' - join expression not supported, i presume this is a limitation of access, is there another way to write it, maybe a nested query?