Sign in to follow this  
Fuzztrek

[web] mysql - excluding results from related tables

Recommended Posts

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 =)

Share this post


Link to post
Share on other sites
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.name
FROM icons i
INNER JOIN rel_icon_keyword r ON r.keyword_id = k.keyword_id
INNER 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.name
FROM icons i
INNER JOIN rel_icon_keyword r ON r.keyword_id = k.keyword_id
INNER 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.name
FROM icons i
INNER JOIN rel_icon_keyword r ON r.keyword_id = k.keyword_id
INNER 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]

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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.name
FROM icons i
INNER JOIN rel_icon_keyword r ON r.icon_id = i.icon_id
INNER 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.

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
SELECT
*
FROM
keywords
LEFT JOIN
rel_icon_keyword ON
rel_icon_keyword.keyword_id = keywords.keyword_id
LEFT JOIN
icon ON
icon.icon_id = rel_icon_keyword.icon_id
WHERE
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
keywords
LEFT JOIN
rel_icon_keyword ON
rel_icon_keyword.keyword_id = keywords.keyword_id
LEFT JOIN
icon ON
icon.icon_id = rel_icon_keyword.icon_id
WHERE
keywords.name = 'x' OR
keywords.name = 'y'


This should work.

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

Share this post


Link to post
Share on other sites
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'

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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:


CREATE TEMPORARY TABLE
rik_temp TYPE=HEAP

SELECT
*
FROM
keywords
LEFT JOIN
rel_icon_keyword ON
rel_icon_keyword.keyword_id = keywords.keyword_id
LEFT JOIN
icons ON
icons.icon_id = rel_icon_keyword.icon_id
WHERE
keywords.name = "x";



Select from temp table:

SELECT DISTINCT
*
FROM
rik_temp
WHERE
name <> "y";



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

Share this post


Link to post
Share on other sites
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'.

Share this post


Link to post
Share on other sites
Sorry about that.


CREATE TEMPORARY TABLE
rik_temp TYPE=HEAP

SELECT
icons.icon_id
FROM
keywords
LEFT JOIN
rel_icon_keyword ON
rel_icon_keyword.keyword_id = keywords.keyword_id
LEFT JOIN
icons ON
icons.icon_id = rel_icon_keyword.icon_id
WHERE
keywords.name = "x";




This gives you a list of all the icon_ids that have an 'x' value. Now we want to try to get rid of those that have a 'y'.


SELECT
*
FROM
rik_temp
RIGHT JOIN
rel_icon_keyword ON
rel_icon_keyword.icon_id = rik_temp.icon_id
RIGHT JOIN
keywords ON
keywords.keyword_id = rel_icon_keyword.keyword_id
WHERE
keywords.name <> 'y';




The right joins SHOULD eliminate all of the icons that have y values from the original list. Hopefully this works but I'm not thinking very well this morning as babies don't leave room for a lot of sleep some nights.

Edit: If this doesn't work then I'll create a database that is layed out the same and make sure I get you the right one next time [Razz].

Share this post


Link to post
Share on other sites
Well, I tried that and I got all 305 icons related to 'x' in addition to every single keyword in the database ;)

Who knew this would be so difficult... if there is a solution, it's completely counter-intuitive.

Thanks for taking an interest in this, though [wink]

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this