I need help writing a complex (to me) SELECT statement - PHP/MySQL

Question

Ok. I'm new to MySQL have a SELECT statement I can't wrap my head around.

I have a table of books with a primary key of ASIN (10-digit ISBN), and I have a table of tags with a auto-incrementing primary key. Then I have a junction table to show which ASINs have which tags associated with them.

I can use this SELECT statement to give any books using a single tag:

SELECT b.asin, b.title, b.img_thumb, b.filename FROM books AS b
INNER JOIN tag_junction AS tj USING (asin)
WHERE tj.tag_id=14 
ORDER BY title

But what I'd like to do is write a query that will give any books that contain multiple tags, both by AND and by OR. I've tried just writing it as follows below but that doesn't work.

SELECT b.asin, b.title, b.img_thumb, b.filename FROM books AS b
INNER JOIN tag_junction AS tj USING (asin)
WHERE tj.tag_id=14 AND tj.tag_id=12 
ORDER BY title

So basically I'd like the query to return any books that use both tag_id 14 and tag_id 12. I'd also like to be able to query books that use either 14 OR 12.

What am I missing? Thanks in advance.

Answer 1

Use:

SELECT b.asin, 
       b.title, 
       b.img_thumb, 
       b.filename 
  FROM BOOKS b
  JOIN TAG_JUNCTION tj ON tj.asin = b.asin
 WHERE tj.tag_id IN (12, 14)

This will return BOOKS records that are associated to:

• tag_id 12
• tag_id 14
• combination of tag_id 12 and tag_id 14

As long as one of them is satisfied, the associated BOOKS record will be returned.

To return where all the tags match, you have to add GROUP BY and HAVING clauses:

SELECT b.asin, 
       b.title, 
       b.img_thumb, 
       b.filename 
  FROM BOOKS b
  JOIN TAG_JUNCTION tj ON tj.asin = b.asin
 WHERE tj.tag_id IN (12, 14)
GROUP BY b.asin, b.title, b.img_thumb, b.filename 
  HAVING COUNT(DISTINCT tj.tag_id) = 2

The number for the count in the HAVING clause MUST MATCH the number of tags specified in the IN clause.

Answer 2

Just doing this should give you all three scenarios:

SELECT b.asin, b.title, b.img_thumb, b.filename FROM books AS b
INNER JOIN tag_junction AS tj USING (asin)
WHERE (tj.tag_id=14 OR tj.tag_id=12) ORDER BY title

Books that have 14 AND 12 also match the criteria of a book that has 14 OR 12 :o)

Answer 3

To sum up what others have said, OR is inclusive, unless specified otherwise. You'll find this to be generally true in Computerland in programming and scripting languages, in bitwise operations at the CPU level, and in gate logic at the hardware level. It's also this way in logic (philosophy).

To become a whiz at ANDs and ORs and other logical things, check out truth tables: http://en.wikipedia.org/wiki/Truth_table.

Answer 4

To match both tags, simply use modified version of queries already posted. Using same query you do for filtering, additionaly check if tags count for given book equals to 2 - this assures, both were matched.

SELECT b.asin, b.title, b.img_thumb, b.filename 
FROM books b
JOIN tag_junction tj ON tj.asin = b.asin
WHERE tj.tag_id IN (12, 14)
AND 
( 
  SELECT COUNT(*) 
  FROM books ib 
  JOIN tag_junction itj ON (itj.asin = ib.asin)
  WHERE itj.tag_id IN (12, 14) AND ib.asin = b.asin
) = 2
GROUP BY b.asin
ORDER BY title

Answer 5

This should work for your AND case. Note that the hard-coded 2 must match the number of IDs in the IN clause:

select *
from books 
where asin in (
    SELECT b.asin
    FROM books b
    INNER JOIN tag_junction tj on b.asin = tj.asin
    WHERE tj.tag_id in (12, 14)
    group by b.asin
    having count(distinct tj.tag_id) = 2
)

For your OR case you can do this:

SELECT b.asin, b.title, b.img_thumb, b.filename
FROM books AS b
INNER JOIN tag_junction tj on b.asin = tj.asin
WHERE tj.tag_id in (12, 14)

Answer 6

use distinct

SELECT distinct b.asin, b.title, b.img_thumb, b.filename
FROM books b JOIN tag_junction tj ON tj.asin = b.asin WHERE tj.tag_id IN (12, 14) AND
(
SELECT COUNT(*)
FROM books ib
JOIN tag_junction itj ON (itj.asin = ib.asin) WHERE itj.tag_id IN (12, 14) AND ib.asin = b.asin ) = 2 GROUP BY b.asin ORDER BY title