MySQL Query Question.. I give up!

*UPDATE, seems like the answer was given, but the SQL query his erroring out.. can anyone help? See the first answer, i posted the problem there.

So to put this simply. I have 3 tables. An "item" table and a "tag" table. Then I also have an "item_tag" table which ties the 2 together.

I want to make a query that lists all the items that have particular tags assigned to it. So I开发者_JAVA百科 would like the query to list all items that have tag x and tag y applied to it.

This is what I have come up with so far.. except that this will list any that match either tag id 148 or tag id 152. If I make it say "AND" it shows no results.

SELECT *
FROM (`item`)
RIGHT OUTER JOIN `item_tag` ON `item`.`id` = `item_tag`.`fk_item_id`
WHERE `item_tag`.`fk_tag_id` = "152" OR `item_tag`.`fk_tag_id` = "148"
GROUP BY `item`.`id`

Using JOINs:

 SELECT it.fk_item_id
   FROM ITEM i
   JOIN ITEM_TAG it1 ON it1.fk_item_id = i.id
                    AND it1.fk_tag_id = 148
   JOIN ITEM_TAG it2 ON it2.fk_item_id = i.id
                    AND it2.fk_tag_id = 152

Using GROUP BY/HAVING COUNT:

  SELECT it.fk_item_id
    FROM ITEM_TAG it
   WHERE it.fk_tag_id IN (148, 152)
GROUP BY it.fk_item_id
  HAVING COUNT(*) = 2

Caveat emptor:
The GROUP BY/HAVING COUNT version of the query is dependent on your data model having a composite key, unique or primary, defined for the two columns involved (fk_item_id and fk_tag_id). If this is not in place, the database will not stop duplicates being added. If duplicate rows are possible in the data, this version can return false positives because an item_id could have 2 associations to the tag_id 148 - which would satisfy the HAVING COUNT(*) = 2.

You can get ids of the items that have all of the tags you want using this query:

SELECT fk_item_id
FROM item_tag
WHERE fk_tag_id IN (5,10,15)
GROUP BY fk_item_id
HAVING COUNT(*) = 3

And then just

SELECT * 
FROM item 
WHERE id 
IN 
(
    SELECT fk_item_id
    FROM item_tag
    WHERE fk_tag_id IN (5,10,15)
    GROUP BY fk_item_id
    HAVING COUNT(*) = 3
)

You just have to modify the ids and the 3 which is the count of those ids.

When your table does not have UNIQUE constraint (it should have) and there can be the same tags in particular item you should modify the query to this:

SELECT * 
FROM item 
WHERE id 
IN 
(
    SELECT fk_item_id
    FROM ( SELECT DISTINCT fk_item_id, fk_tag_id FROM item_tag ) someAlias
    WHERE fk_tag_id IN (5,10,15)
    GROUP BY fk_item_id
    HAVING COUNT(*) = 3
)

I put system in the tem table and programing language in tags. Using this query you have this result:

SELECT *
FROM (`item`)
INNER JOIN `item_tag` ON `item`.`id` = `item_tag`.`fk_item_id`
INNER JOIN `tag` ON `item_tag`.`fk_tag_id` = `tag`.`id`
WHERE `tag`.`desc` = 'Java' or `tag`.`desc` = 'C++'

Result:

1, 'Sistem A', 1, 1, 1, 'Java'
1, 'Sistem A', 1, 3, 3, 'C++'
2, 'Sistem B', 2, 1, 1, 'Java'
2, 'Sistem B', 2, 3, 3, 'C++'

In this case, system A and System B use Java and C++. To simplify use distinct clause

SELECT distinct item.desc
FROM (`item`)
INNER JOIN `item_tag` ON `item`.`id` = `item_tag`.`fk_item_id`
INNER JOIN `tag` ON `item_tag`.`fk_tag_id` = `tag`.`id`
WHERE `tag`.`desc` = 'Java' or `tag`.`desc` = 'C++'

Result:

System A
System B

I am hoping that this will model your tables ( you mention 3 tables but I only have 2 in this )

-- drop table item ;
-- drop table item_tag ;

create table item (
    id int not null auto_increment 
    , primary key ( id )
);
create table item_tag (
    fk_item_id int not null
,   fk_tag_id int not null
);

insert into item values ( 1 );
insert into item values ( 2 );
insert into item values ( 3 );

insert into item_tag values ( 1, 148 );
insert into item_tag values ( 1, 152 );

insert into item_tag values ( 2, 148 );

insert into item_tag values ( 3, 152 );

select i.id, a.fk_tag_id, b.fk_tag_id
from item i, item_tag a, item_tag b
where i.id = a.fk_item_id 
  and i.id = b.fk_item_id 
  and a.fk_tag_id = 148 
  and b.fk_tag_id = 152 
;

Produces the output

+----+-----------+-----------+
| id | fk_tag_id | fk_tag_id |
+----+-----------+-----------+
|  1 |       148 |       152 | 
+----+-----------+-----------+
1 row in set (0.00 sec)

select * from item, item_tag a, item_tag b
where item.id = a.fk_item_id and a.fk_tag_id = 148 and
item.id = b.fk_item_id and b.fk_tag_id = 152;

select * from item where item.id = itemtag.fk_item_id and item_tag.fk_tag_id = "152" or item_tag.fk_tag_id = "148" group by item.id