MySQL sadness - Teh Forum

CodingMySQL sadness

From:	steve	24 Mar 2011 00:46
To:	ALL	1 of 17

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 :-

code:

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 :-

code:

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 :(

0/0

More

From:	Peter (BOUGHTONP)	24 Mar 2011 01:27
To:	steve	2 of 17

38315.2 In reply to 38315.1
SQL code: 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' `3.1415P265E589T932E846R64338`

0/0

More

From:	Peter (BOUGHTONP)	24 Mar 2011 01:28
To:	Mouse	3 of 17

38315.3 In reply to 38315.2
Steve says you're not nice. `3.1415P265E589T932E846R64338`

0/0

More

From:	steve	24 Mar 2011 01:31
To:	Peter (BOUGHTONP)	4 of 17

38315.4 In reply to 38315.2
Thank you dude! At exactly the same time a friend on The Facebook sent me this :- MySQL code: 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)

0/0

More

From:	steve	24 Mar 2011 01:31
To:	Peter (BOUGHTONP)	5 of 17

38315.5 In reply to 38315.3
:C

0/0

More

From:	Peter (BOUGHTONP)	24 Mar 2011 01:44
To:	steve	6 of 17

38315.6 In reply to 38315.4
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?) `3.1415P265E589T932E846R64338`

0/0

More

From:	steve	24 Mar 2011 01:46
To:	Peter (BOUGHTONP)	7 of 17

38315.7 In reply to 38315.6
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

0/0

More

From:	Peter (BOUGHTONP)	24 Mar 2011 01:53
To:	steve	8 of 17

38315.8 In reply to 38315.7
Bah, you had me excited for a moment then. That's hardly an argument! :( `3.1415P265E589T932E846R64338`

0/0

More

From:	steve	24 Mar 2011 01:54
To:	Peter (BOUGHTONP)	9 of 17

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

0/0

More

From:	CHYRON (DSMITHHFX)	24 Mar 2011 02:02
To:	Peter (BOUGHTONP)	10 of 17

38315.10 In reply to 38315.8
You need help... ---- "We've just seen a pretty convincing demonstration of the safety of nuclear power"

0/0

More

From:	Peter (BOUGHTONP)	24 Mar 2011 02:05
To:	CHYRON (DSMITHHFX)	11 of 17

38315.11 In reply to 38315.10
It's taken you this long to realise? `3.1415P265E589T932E846R64338`

0/0

More

From:	CHYRON (DSMITHHFX)	24 Mar 2011 02:27
To:	Peter (BOUGHTONP)	12 of 17

38315.12 In reply to 38315.11
:-@ ---- "We've just seen a pretty convincing demonstration of the safety of nuclear power"

0/0

More

From:	99% of gargoyles look like (MR_BASTARD)	24 Mar 2011 10:26
To:	steve	13 of 17

38315.13 In reply to 38315.1

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

code:

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

TABLE 2

code:

TagID	Tag
1	nice
2	bitey
3	fuzzy

TABLE 3

code:

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

SQL (pronounced 'sequel')

sql code:

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';

bastard by name, bastard by nature

0/0

More

From:	Peter (BOUGHTONP)	24 Mar 2011 14:45
To:	99% of gargoyles look like (MR_BASTARD)	14 of 17

38315.14 In reply to 38315.13
t3.Tag can't be two things at once. `3.1415P265E589T932E846R64338`

0/0

More

From:	99% of gargoyles look like (MR_BASTARD)	24 Mar 2011 15:13
To:	Peter (BOUGHTONP)	15 of 17

38315.15 In reply to 38315.14
Maybe the 'AND' should be 'OR' <shrug> bastard by name, bastard by nature

0/0

More

From:	Peter (BOUGHTONP)	24 Mar 2011 15:20
To:	99% of gargoyles look like (MR_BASTARD)	16 of 17

38315.16 In reply to 38315.15
And then he'd need the group by/having again, to restrict it to items with both. `3.1415P265E589T932E846R64338`

0/0

More

From:	steve	24 Mar 2011 20:07
To:	99% of gargoyles look like (MR_BASTARD)	17 of 17

38315.17 In reply to 38315.13
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).

0/0

More

Reply to All

Adjust text size : Smaller 10 Larger

Beehive Forum 1.5.2 | FAQ | Docs | Support | Donate!

©2002 - 2024 Project Beehive Forum

Forum Stats