Tricky Unmatched Query

Good2CU

I have tables, site, tag, and ex_tag, the pertinent fields are url in the site table as a primary key, and url / tag in the other 2 tables, as a compound key. (tag is if a tag applies to a given url, and ex_tag is if it doesn't)

I want to run a query that will select any url from the site table that is not already in either tag or ex_tag table for a given tag. The tricky thing is, the url can be in each tag table multiple times, or 0.

I have the following query, but it returns some url records multiple times, and then once all url's should be in either table for a given tag, they will still come up again, giving me a primary key violation:

SELECT s.url FROM (site s LEFT JOIN tag t ON s.ur = t.url)
LEFT JOIN ex_tag x ON s.url = x.url
WHERE ((t.tag != '{$tag}' OR t.url IS NULL)
AND (x.tag != '{$tag}' OR x.url IS NULL))

sfullman

I haven't studied your WHERE clause as it might be a workaround - the following should get you a list of URL's which aren't used in either table:

SELECT s.url,
COUNT(DISTINCT t.url) AS tagcount,
COUNT(DISTINCT x.url) AS xtagcount,
/* and of course.. */
COUNT(DISTINCT t.url) + COUNT(DISTINCT x.url) AS IsUsed
FROM site s LEFT JOIN tag t ON s.url=t.url LEFT JOIN ex_tag x ON s.url=x.url
GROUP BY s.url
HAVING COUNT(DISTINCT t.url) + COUNT(DISTINCT x.url)=0

Use the where clause as desired; HAVING looks at the qualities of the results AFTER they've been grouped.

ONE MORE THING.

If you are joining on a text url (esp. without indexing it) your queries might be slow. suggest you have a nother table called url
urlid int(11) auto_increment primary key,
url char(255) //index this of course

and insert urlid into the other tables vs. url - it will save you space as well