[SOLVED] Selecting and ignoring issue - plz help

[nezbie]

Hi

 

I have a table consisting of following data:

 

id

name

languages_id

 

None of those fields are unique, so one id can have multiple languages_id's.. My problem is, that I'd need to select all id's from the table where languages_id has a value of x, but not y.. So the idea in php is to copy all the fields to language y if it does not exists.. Example:

 

id            name              languages_id

10          frontpage        1

10          startseite        2

20          information      1

 

id 20 does not exists in german (languages_id = 2), select this id. I've tried like (as simple as I thought it COULD be.. wasn't):

 

SELECT DISTINCT id FROM table WHERE languages_id = "1" AND languages_id != "2"

 

I'll try to work this out

 

[Illusion]

try this

SELECT DISTINCT id FROM table WHERE languages_id = "1" AND id NOT IN (SELECT DISTINCT id FROM table WHERE languages_id = "2"

tell me is it working fine or you got any syntax error.

[fenway]

A self join would work too...

[nezbie]

SELECT DISTINCT id FROM table WHERE languages_id = "1" AND id NOT IN (SELECT DISTINCT id FROM table WHERE languages_id = "2") works fine - thanx for both answer, really appreciate it!