Over the past few days I've discovered what I assume (naturally [wink]) to be short comings in SQL or at least MySQL. Most of them I've been able to work around with a bit of array sorting in PHP (though I'm sure there must be a more efficient method in SQL), and although I could do that now with this problem I'd rather not. I have 3 tables set up, keywords, icons, and rel_icon_keyword. Each icon can have multiple keywors with the relation defined in rel_icon_keyword. Now, so suppose I have keywords "x" and "y". The icons I'm interested in are all related to "x". Some of them are related to keyword "y". I've been successful in selecting all icons related with "x", or all icons related with "y", but not "x" and "y" together or "x" but not "y". My first thought was do to something like: SELECT keywords.name FROM keywords, icons, rel_icon_keyword WHERE keywords.keyword_id = rel_icon_keyword.keyword_id AND icons.icon_id = rel_icon_keyword.icon_id AND keywords.name = 'x' AND keywords.name != 'y' ORDER BY icons.icon_id But that of course does not work. PLEASE tell me there is some way to do this in MySQL - I'd rather not loop through the entire selection in PHP, checking each record against my conditions :/ As I mentioned I am doing this already, but in those cases I'm not dealing with upwards of 350 records, just a handful which isn't so bad (but would probably be more elegant in SQL). Thanks in advance =)

I'm not sure I really know what your trying to do, so here are a couple of suggestions. I hope at least one of them is helpful:

Select all icons related to the keyword "x":

SELECT i.nameFROM icons iINNER JOIN rel_icon_keyword r ON r.keyword_id = k.keyword_idINNER JOIN keywords k ON k.keyword_id = r.keyword_id and k.name = 'x'

Select all icons not related to the keyword "x" or "y":

SELECT i.nameFROM icons iINNER JOIN rel_icon_keyword r ON r.keyword_id = k.keyword_idINNER JOIN keywords k ON k.keyword_id = r.keyword_id and k.name <> 'x' and k.name <> 'y'

Select all icons once not related to the keyword "y":

SELECT DISTINCT(i.icon_id), i.nameFROM icons iINNER JOIN rel_icon_keyword r ON r.keyword_id = k.keyword_idINNER JOIN keywords k ON k.keyword_id = r.keyword_id and k.name <> 'y'

As you can see I'm rather fond of joins, but that's just me.. [smile]

Thanks for the reply, WanMaster!

I figured out how to retrieve icons related to both keywords 'x' AND 'y' using the following query:

SELECT DISTINCT i.filename
FROM icons i, keywords k1, keywords k2
INNER JOIN rel_icon_keyword rik1
ON i.icon_id = rik1.icon_id
AND rik1.keyword_id = k1.keyword_id
INNER JOIN rel_icon_keyword rik2
ON i.icon_id = rik2.icon_id
AND rik2.keyword_id = k2.keyword_id
WHERE k1.name = 'X'
AND k2.name = 'Y'

However, what I'm having trouble with is retrieve icons related to keyword 'x' but not 'y'. Currently, all (305) icons in my database are related to 'x', but only some (44) are related to 'y'. That is, icons have a one to many relationship with keywords. I can get those 44 icons related to both 'x' and 'y' with the query above. Is there any way I can retrieve the 261 icons related to 'x' but *not* 'y'?

I've tried a bunch of stuff, but no matter what happens I end up with the 305 icons related to 'x' (even if they are also related to 'y').

Thanks for your time, I'm fond of joins too :o!

I finally think I know what your problem is. [smile]
In this one, it will skip every icon that has a record in rel_icon_keyword with the keyword "y". I haven't tested it, so I could be wrong.

SELECT i.icon_id, i.name, k.keyword_id, k.nameFROM icons iINNER JOIN rel_icon_keyword r ON r.icon_id = i.icon_idINNER JOIN keywords k ON k.keyword_id = r.keyword_id AND k.name <> 'y'WHERE i.icon_id NOT IN (SELECT r2.icon_id FROM rel_icon_keyword r2 WHERE r2.keyword_id <> k.keyword_id)

However, the subquery at the end is only supported in MySQL 4.1 and above.

I actually thought of using a subquery, but of course I'm still running mysql 4.0 something or other :/ Guess I'm out of luck on this one, thanks anyway for your time =)

I guess I'll just introduce another keyword 'z' and relate to all the keywords that aren't related to keyword 'y'.. or maybe I should badger my host to upgrade MySQL...

SELECT	*FROM	keywordsLEFT JOIN	rel_icon_keyword ON		rel_icon_keyword.keyword_id = keywords.keyword_idLEFT JOIN	icon ON		icon.icon_id = rel_icon_keyword.icon_idWHERE	keywords.name = 'x'

That should select everything that has a keyword of x and only x. If you want both x and y then:

SELECT	*FROM	keywordsLEFT JOIN	rel_icon_keyword ON		rel_icon_keyword.keyword_id = keywords.keyword_idLEFT JOIN	icon ON		icon.icon_id = rel_icon_keyword.icon_idWHERE	keywords.name = 'x' OR	keywords.name = 'y'

This should work.

Edit: Misunderstood the problem, back to the drawing board!

You should be able to select into a temporary table and then select what you need from that. To get only 'x' you would select * joined where keyword.name = 'x' then you would select from the temp table where keyword.name <> 'y'

Quote:Original post by tstrimp
You should be able to select into a temporary table and then select what you need from that. To get only 'x' you would select * joined where keyword.name = 'x' then you would select from the temp table where keyword.name <> 'y'

Ah good thinking, thanks! I tried creating a temporary table, which worked fine:

CREATE TEMPORARY TABLE rik_temp TYPE=HEAP SELECT rel_icon_keyword.* FROM rel_icon_keyword, keywords WHERE keywords.keyword_id = rel_icon_keyword.keyword_id AND keywords.name = "x"

However, I can't seem to get the select statement to work. I either end up with 305, 44, or 0 relations :/
SELECT DISTINCT rik_temp.* FROM rik_temp, keywords, rel_icon_keyword WHERE rik_temp.icon_id = rel_icon_keyword.icon_id AND rel_icon_keyword.keyword_id = keywords.keyword_id AND keywords.name <> "y"

edit: I also talked to my host, here's what he said when I asked about upgrading to MySQL 4.1:

Quote:The current version of MySQL is 4.0.24. Due to Cpanel being what runs almost everything, it's impossible to upgrade MySQL at this point in time without taking a risk of anything going wrong.

I too would like both the upgraded version of MySQL and PHP, but until Cpanel launched their new version, it can't be upgraded just yet, however, I am working on it, and Cpanel should release soon.

You should only need to select from the temp table. First you select into it all of the fields that you need then you just limit that based on the keyword.

Create Temp Table:



Select from temp table:


You probably don't want to select * but I'm not sure what fields you actualy want.

Hmm. In the first SELECT statement above, you are only populating the temporary table with fields where the keyword name is 'x' - so when you go and select from the temporary table, everything passes the condition because 'x' <> 'y'.