Finding unique ids from a table according to another 1

[Sen7inel]

Hiya,

For some reason I fail to see an easy solution for this: I have two tables. Table1 is id. Table2 is id, Table1_id, Some_id.

Now, I want to find all id's from Table1 that DO NOT have a certain Some_id in Table2, AND id's from Table1 that do not appear in Table2 at all.

Example:

Table1 (id)
1
2
3

Table2 (id, Table1_id, Some_id)
1 | 2 | 6
2 | 2 | 4
3 | 1 | 4

The select statement uses Some_id as a parameter (querying from php) and should return the ids with conditions mentioned above. So, for example, if I do SELECT whatever .. Some_id = (or !=) 6, I would get Table1 ids 2 and 3. If Some_id would be 4 I'd get 1. If Some_id'd be 10, I'd get 1, 2 and 3. And I would get them only ONCE.. Sorry for the messed up explanation, couldn't do better.

 

[TonyGroves]

If the tables are joined on table1.id=table2.table1_id, then the following should work:
[tt]
SELECT table1.id
FROM
table1
LEFT JOIN table2
ON
table1.id=table2.table1_id
AND table2.some_id=6
WHERE table2.table1_id IS NULL
[/tt]
That would return id's 1 and 3, not 2 and 3.

If the tables are joined on table1.id=table2.id, then the following should work:
[tt]
SELECT table1.id
FROM
table1
LEFT JOIN table2
ON table1.id=table2.id AND table2.some_id=6
WHERE table2.id IS NULL
[/tt]
That would return id's 2 and 3.



-----
ALTER world DROP injustice, ADD peace;