MySQL Query question .. I give up!

Question

* UPDATE, it looks like the answer was given, but the SQL query is its error .. can anyone help? See the first answer, I posted the problem there.

So easy to say. I have 3 tables. The table "item" and the table "tag". Then I also have a table called "item_tag" that links 2 together.

I want to make a query that lists all the elements that are assigned specific tags. Therefore, I would like the query to list all the elements to which the x and y tags are applied.

This is what I have come up with so far, except that it will list any that match either id 148 or id 152. If I say AND, it won’t show 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`

Answer 1

, , , :

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

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
)

3, .

UNIQUE ( ), , :

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
)

Answer 2

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

GROUP BY/HOWING 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

emptor:
GROUP BY/HAVING COUNT , , , (fk_item_id fk_tag_id). , . , , item_id 2 tag_id 148 - HAVING COUNT(*) = 2.

Answer 3

. , :

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++'

:

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++'

A System B Java ++.

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++'

:

System A
System B

Answer 4

, ( 3 , 2 )

-- 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 
;

Outputs

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

Answer 5

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;

Answer 6

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