MySQL - Searching 2 Tables

[Vulcan]

Tables:

[b]items[/b]
itemid | name
1      | Cow
2      | Small Cow
3      | Sheep
4      | House

[b]tags[/b]
itemid | tag
2      | Milk
2      | Grass
3      | Cow

Query:

SELECT * FROM items LEFT JOIN tags ON items.itemid = tags.itemid WHERE item.name LIKE '%cow%' OR tags.tag LIKE '%cow%'

I want it to return rows 1,2,3 from items, because those 3 contain "cow" in either the name or tag.

However, it is returning 1,2,2,3 from items, because item 2 has 2 tags assigned to it.

Solution? The tables can be altered if need be.

 

[andshrew]

This is because your requesting everything that meets the criteria.

If you only want those rows from the items table then you will need to select those columns and then use the distinct function to make it only show the item once. ie. SELECT DISTINCT items.itemid, items.name

 

[Haircut]

This is because your requesting everything that meets the criteria.

If you only want those rows from the items table then you will need to select those columns and then use the distinct function to make it only show the item once. ie. SELECT DISTINCT items.itemid, items.name

I don't necessarily think DISTINCT is the way to go with this.
I'd personally do something like this:

SELECT *
FROM items i
WHERE i.name LIKE '%cow%'
OR EXISTS
(SELECT 1
 FROM tags t
 WHERE t.itemid = i.itemid
 AND t.tag LIKE '%cow%'
)

which seems to be what the OP is trying to query for.

 

[Vulcan]

That works great.

Thank you both for your input.