Mysql Query Help - Needed

Question

Table : taxonomy_term_data

tid   name
7     desert
10    indian

Table : taxonomy_index

nid   tid
13     7
10     7
10     10
12     7
14     10

I need a mysql query to get the nid which has "desert" and also "Indian". Here the output is nid = 10. can someone help in writing query. I'm just a beginner

Answer 1

SELECT nid
FROM  taxonomy_index
JOIN taxonomy_term_data USING (tid)
WHERE name IN ('desert', 'indian')
GROUP BY nid
HAVING COUNT(DISTINCT tid)  = 2

See it working online: sqlfiddle

Answer 2

You can get this answer via GROUP BY and HAVING:

SELECT nid
FROM taxonomy_index
WHERE tid IN (7, 10)
GROUP BY nid
HAVING COUNT(*) > 1

Answer 3

you can writ it in various SQL writing style as follow:

1.

SELECT DISTINCT(i.nid)
FROM taxonomy_index i
INNER JOIN taxonomy_term_data d
ON i.tid = d.tid
AND (d.name = 'desert'
OR d.name = 'indian')

2.

SELECT i.nid
FROM taxonomy_index i
INNER JOIN taxonomy_term_data d
ON i.tid = d.tid
AND (d.name = 'desert'
OR d.name = 'indian')
GROUP BY nid

3.

SELECT i.nid
FROM taxonomy_index i, taxonomy_term_data d
WHERE i.tid = d.tid
AND d.name IN ('desert', 'indian')
GROUP BY nid

4.

SELECT DISTINCT(nid)
FROM taxonomy_index
WHERE (tid = 7 
OR tid = 12)

Answer 4

Assuming that the (nid, tid) combination is unique:

SELECT ta.nid
FROM taxonomy_index AS ta
  JOIN taxonomy_index AS tb
    ON tb.nid = ta.nid
WHERE ta.tid = 7
  AND tb.tid = 10