Hi I cant determine worthwhile method of getting a listing of tag.names and lang which is not already placed together inside images_web addresses.

My database looks something similar to this.


tags

  title    user_id

+-------+---------+

hi         1    

friend     1    

friend     2    

people     2    

+-------+---------+

customers

 id   lang

+---+------+

 1   en  

 2   sv  

+---+------+

images_web addresses

  title     lang

+--------+------+

 hi       en  

 friend   sv  

+--------+------+

What I must have came back could be:


result

 title     lang

+-------+------+

friend   en  

people   sv  

+-------+------+

I've attempted something similar to 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.

I am unsure why images_urls includes a title rather than a user_id, but this will 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

While using LEFT JOIN returns NULL for rows that don't appear in images_urls, and so i check for your.

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 recommend images_web addresses should contain user_id not title, but thats another problem.

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

can also be valid for testing reasons.