SQL queries about multiple related tags and items relationships

Question

I've searched in SO for some of the following queries, but I can't find something that match my search, and I'm a little bit lost between IN, INNER JOIN and LEFT JOIN to write them.

The current database relationships

1. Get the items by tags

TAG.url are strings got from a $_GET, so not INT ids.

For example, with the url mydomain/items/tag1+tag2/, we'll have the following requests.

For one tag that was easy but for two or more, the following doesn't seems to work in mySQL :

SELECT 
    ITEM.id AS 'item_id',
    ITEM.title AS 'item_title',
    ITEM.content AS 'item_content'
  FROM ITEM, ITEM_TAG, TAG
WHERE
  (ITEM.id=ITEM_TAG.id_item AND TAG.id=ITEM_TAG.id_dtag AND TAG.url='tag1')
AND
  (ITEM.id=ITEM_TAG.id_item AND TAG.id=ITEM_TAG.id_dtag AND TAG.url='tag2')
...
AND
  (ITEM.id=ITEM_TAG.id_item AND TAG.id=ITEM_TAG.id_dtag AND TAG.url='tagn')

This one works :

SELECT 
  ITEM.id AS 'item_id',
  ITEM.title AS 'item_title',
  ITEM.content AS 'item_content'
FROM ITEM
  INNER JOIN TAG TAG1
   ON TAG1.url='tag1'
  INNER JOIN ITEM_TAG ITEM_TAG1
   ON ITEM_TAG1.id_item=ITEM.id AND ITEM_TAG1.id_tag=TAG1.id
  INNER JOIN TAG TAG2
   ON TAG2.url='tag2'
  INNER JOIN ITEM_TAG ITEM_TAG2
   ON ITEM_TAG2.id_item=ITEM.id AND ITEM_TAG2.id_tag=TAG2.id
  ...
  INNER JOIN TAG TAGn
   ON TAG2.url='tagn'
  INNER JOIN ITEM_TAG ITEM_TAGn
   ON ITEM_TAGn.id_item=ITEM.id AND ITEM_TAGn.id_tag=TAGn.id 

At this point, is there a better way to do that, than those INNER JOIN and ON ?

2. Get the related tags for other tags

Here I would like to get the list of tags and the number of items that match other tags.

For example, let's imagine the following fixtures :

    ITEM_TAG             TAG
id_item | id_tag       id | url
1       | 1            1  | tag1
1       | 2            2  | tag2
1       | 3            3  | tag3
1       | 4            4  | tag4
2       | 1
2       | 2
2       | 3
3       | 1
3       | 2

Always with the following search mydomain/items/tag1+tag2/.

I wish to display the other tags with a relation with some items (if those relations exist).

I'm sorry to say that I have no idea on how to make a request that will output the following :

    TAG
url    count(id_item)
tag3 | 2
tag4 | 1

How to get this result ?

Edit

I tried the following wich seems to work :

SELECT 
  COUNT(id_item) AS 'nb_item', 
  TAG.url AS 'tag_url'
FROM ITEM_TAG, TAG
WHERE 
 url NOT IN ('tag1','tag2')
 AND id_item IN 
 (
   SELECT id_item FROM ITEM_TAG, TAG 
   WHERE url IN ('tag1','tag2') AND id_tag=id
 )
 AND id_tag=id
GROUP BY TAG.id ORDER BY nb_item DESC

Any idea or advise to optimize this will be greatly appriciated.

Answer 1

1. Get the items by tags

select  i.id
,       i.title
,       i.content
from    item i
join    item_tag it
on      i.id = it.id_item
join    tag t
on      t.id = it.id_tag
where   t.url in ('tag1', 'tag2')

2. Get the related tags for other tags

select  t2.url
,       count(it2.id_item)
from    tag t1 -- this tag
join    item_tag it1
on      t1.id = it.id_tag
join    item_tag it2 -- other tag for same item
on      it2.item_id = it1.item_id
join    tag t2
on      t2.id = it2.id_tag
where   t1.url in ('tag1', 'tag2')
        and t2.url not in ('tag1', 'tag2')
group by
        t2.url