MySQL query help

Question

Hi I cant figure out any good way to get a list of tag.names and lang which isn't already inserted in combination inside images_urls.

My database looks something like this.

tags

  name    user_id
+-------+---------+
|hi     |    1    | 
|friend |    1    |
|friend |    2    |
|people |    2    |
+-------+---------+

users

 id   lang
+---+------+
| 1 |  en  |
| 2 |  sv  |
+---+------+

images_urls

  name     lang
+--------+------+
| hi     |  en  |
| friend |  sv  |
+--------+------+

What I would like to have returned would be:

result

 name     lang
+-------+------+
|friend |  en  |
|people |  sv  |
+-------+------+

I have tried something like this:

SELECT tags.name, users.lang
FROM tags, users
WHERE tags.user_id = users.id
AND CONCAT(tags.name, ',', users.lang) NOT IN(
    SELECT DISTINCT CONCAT(name, ',', lang) FROM images_urls
)
GROUP BY CONCAT(name, ',', lang)
ORDER BY SUM(tags.count) DESC
LIMIT 20;

Thanks.

Answer 1

SELECT tags.name, users.lang
FROM tags, users
WHERE tags.user_id = users.id
AND users.name NOT IN (SELECT name from images_urls);

I would suggest images_urls should contain user_id not name, but thats a different issue.

column NOT IN ('name`','name1','name2','name3')

is also valid for testing purposes.

Answer 2

I'm not sure why images_urls has a name instead of a user_id, but this should work:

SELECT t.name, u.lang
FROM tags t
JOIN users u ON ( u.id = t.user_id )
LEFT JOIN images_urls iu ON ( iu.name=t.name AND iu.lang=u.lang )
WHERE iu.name IS NULL

Using the LEFT JOIN returns NULL for rows that do not exist in images_urls, so I check for that.