Mysql Join Query gives the same results multiple times

Question

Hi,

I have two tables. One is items, and one istags. Query's here.

The idea is to create a search box to search through the items and then narrow down the search by selecting tags. (Some kind of faceted search).

After fiddling around with mysql tutorials i came up with this:

SELECT `items`.`id`, `items`.`name`, `items`.`pay`, `items`.`desc`
FROM `items`LEFT JOIN (`tags`) ON (`tags`.`item_id`=`items`.`id`) 
WHERE (
    (`tags`.`type`='food' AND `tags`.`name`='pizza')
    OR (`tags`.`type`='drinks' AND `tags`.`name`='lemonade')
    --And so on for every tag
)
ORDER BY `pay` DESC LIMIT 0 , 30

(I removed the full-text search in the query because it isn't relevant right now)

It works pretty good, but I have two problems:

1. If the user doesn't select any tags, every item that has more then 1 tag attached to it shows up more then 1 time
2. If the user selects two tags, food=>pizza and drinks=>lemonade by example, every item with pizza or lemonade or both will show up, I only want the items that have BOTH those tags attached to it.

Is it possible to solve these problems with a different mysql query? Or should I fix these problems in the php code. (Or am I doing this totally wrong, and is there a much better way to use tags?)

Hopefully I made myself clear.

Gr,

Bas

Answer 1

Try WHERE tag.id IN (SUBQUERY)

SELECT `items`.`id`, `items`.`name`, `items`.`pay`, `items`.`desc`
FROM `items`  WHERE items.id IN (SELECT DISTINCT item_id
   FROM tags
   WHERE (
    (`tags`.`type`='food' AND `tags`.`name`='pizza')
    OR (`tags`.`type`='drinks' AND `tags`.`name`='lemonade'))
    --And so on for every tag
)
ORDER BY `pay` DESC LIMIT 0 , 30

Answer 2

Throw in the keyword DISTINCT after SELECT?

SELECT DISTINCT `items`.`id`, `items`.`name`, `items`.`pay`, `items`.`desc` 
FROM `items`LEFT JOIN (`tags`) ON (`tags`.`item_id`=`items`.`id`)  
WHERE ( 
    (`tags`.`type`='food' AND `tags`.`name`='pizza') 
    OR (`tags`.`type`='drinks' AND `tags`.`name`='lemonade') 
    --And so on for every tag 
) 
ORDER BY `pay` DESC LIMIT 0 , 30 

As for your second issue... your logic is just wrong. If I understand you correctly you want to group by items.id and then reject the items that don't come back with the same number of rows as the number of selected tags... maybe something like:

SELECT DISTINCT `items`.`id`, `items`.`name`, `items`.`pay`, `items`.`desc` 
    FROM `items`LEFT JOIN (`tags`) ON (`tags`.`item_id`=`items`.`id`)  
    WHERE ( 
        (`tags`.`type`='food' AND `tags`.`name`='pizza') 
        OR (`tags`.`type`='drinks' AND `tags`.`name`='lemonade') 
        --And so on for every tag 
    ) 
    GROUP BY `items`.`id` 
    HAVING COUNT(*) = 2 
    ORDER BY `pay` DESC LIMIT 0 , 30 

... and replace the 2 in HAVING COUNT(*) = 2 with the number of tags you are trying to concurrently match ...