Hello :C

This is, I'm certain, a really simple question. I can do it with several pointlessly long queries and PHP inbetween, but I know there's a way to do it in one query. I just can't remember.

I have one table, called 'images'. This goes like this :-

ImageID    Image
1          cat.jpg
2          mouse.jpg
3          hamster.jpg
4          dog.jpg

And then I have one called 'tags' which is like this :-

Tag           ImageID
"nice"        1
"nice"        4
"bitey"       2
"bitey"       3
"fuzzy"       1
"fuzzy"       2
"fuzzy"       3
"fuzzy"       4

I want a query that says :-

"SELECT * from IMAGES where.... there is a 'tag' for both fuzzy AND bitey.

And it will return me the mouse and the hamster.

Is this possible? Have I gone mad? Help :(

SELECT i.ImageId , i.Image
FROM images i
JOIN tags t_f ON i.ImageId = t_f.ImageId AND t_f.Tag = 'fuzzy'
JOIN tags t_b ON i.ImageId = t_b.ImageId AND t_b.Tag = 'bitey'

Steve says you're not nice.

Thank you dude!

At *exactly the same time* a friend on The Facebook sent me this :-

SELECT i.imageid, i.thumb, i.title
FROM Images i
JOIN tags t ON i.imageID = t.imageID
WHERE t.tag IN ('fuzzy', 'bitey')
GROUP by i.imageId
HAVING COUNT(*) = 2

Both of yours work, which would be most efficient? Giving that the number of matching tags could end up as high as 6,7?

(heart)

:C

I considered that way, but figured grouping/counting would be extra work ... but I'm not sure if that's actually the case.

How important is efficiency? If you need to handle potentially heavy traffic (like >1 request per second), I think I'd want to do the lookup a different way entirely.

How many tags will there be in total (i.e. will you have 7 tags and might have all of them matching, or will the 7 matching tags be out of a hundred possible tags?)

Likey to be 7 tags out of maybe 30. I'm designing this specifically for small scale.

I think an SQL-based argument is about to break out on my Facebook post, if you want to observe/join it :D

Bah, you had me excited for a moment then. That's hardly an argument! :(

I reckon they're frantically testing the query for efficiently.etc and it'll get exciting soon!

You need help...

It's taken you this long to realise?

:-@

Your tags should be in a separate table, with a cross-table between the two. I haven't tested this (and don't have the means to ATM), so would likely need 'tweaking' (possibly needs JOIN LEFT):

TABLE 1

ImageID	Image
1	cat.jpg
2	mouse.jpg
3	hamster.jpg
4	dog.jpg

TABLE 2

TagID	Tag
1	nice
2	bitey
3	fuzzy

TABLE 3

ImageID	TagID
1	1
4	1
2	2
3	2
1	3
2	3
3	3
4	3

SQL (pronounced 'sequel')

SELECT t1.* FROM images t1
JOIN crosstab t2 ON t1.ImageID=t2.ImageID
JOIN tags t3 ON t2.TagID=t3.TagID
WHERE t3.Tag='fuzzy' AND t3.Tag='bitey';

t3.Tag can't be two things at once.

Maybe the 'AND' should be 'OR'
<shrug>

And then he'd need the group by/having again, to restrict it to items with both.

I realised it'd be better to have the tags work like that, but like I said - it's a very small-scale script and there are benefits to doing it this way (when the last image tagged as something has it's tag removed, that option vanishes altogether.etc).