Only compare values if not null?

[Fog Juice]

Is it possible to only compare values in a WHERE clause if one of the values exists?

 

For example,

SELECT a.column, b.column 
FROM table a, table b 
WHERE a.some_id = b.id

 

If b.id does not exist, is it possible to skip that whole comparison?

 

 

Thanks.

[Fog Juice]

Or how would I select a value from another table only if a value in a different table doesn't exist?

[DavidAM]

If I understand, you want the records from table A where the ID does not exist in table B?

 

SELECT a.column, b.column
FROM table a LEFT JOIN table b ON a.some_id = b.id
WHERE b.id IS NULL

 

with an outer join, when the record is not found, all columns from that table will be NULL (as far as that row in the result is concerned).

 

An alternate solution (might run faster depending on the tables):

 

SELECT a.column
FROM table a
WHERE NOT EXISTS (SELECT * FROM table b WHERE b.id = a.some_id)

[Fog Juice]

If I understand, you want the records from table A where the ID does not exist in table B?

 

SELECT a.column, b.column
FROM table a LEFT JOIN table b ON a.some_id = b.id
WHERE b.id IS NULL

 

with an outer join, when the record is not found, all columns from that table will be NULL (as far as that row in the result is concerned).

 

An alternate solution (might run faster depending on the tables):

 

SELECT a.column
FROM table a
WHERE NOT EXISTS (SELECT * FROM table b WHERE b.id = a.some_id)

 

I've been looking at NOT EXISTS but what I don't understand is how can I use it to still display all the other values in a table. Below I want to select only the values from table2 that are not in table1. So that means only rows with id 5 and 6 would be selected. How can I do this? I think it is probably easy but i'm just having a hiccup.

 

For example,

Table1:

id - id_table2

1 - 1

2 - 2

3 - 3

4 - 4

 

Table2:

id - desc

1 - "hey"

2 - "hello"

3 - "sup"

4 - "example"

5 - "continues"

6 - "on"

 

 

I should also add that this is a select with probably 6 different tables, I just simplified it for here.

[fenway]

LEFT JOINs are easier to write and easier for mysql to figure out.

[Fog Juice]

LEFT JOINs are easier to write and easier for mysql to figure out.

 

Can you explain to me how to do it on my table1/table2 explanation? Sorry I do not quite understand even after reading some tutorials. :'(

[fenway]

Try this:

 

SELECT t2.*
FROM table2 AS t2
LEFT JOIN table1 AS t1 ON ( t1.id_table2 = t2.id )
WHERE t1.id_table2 IS NULL

[Fog Juice]

Try this:

 

SELECT t2.*
FROM table2 AS t2
LEFT JOIN table1 AS t1 ON ( t1.id_table2 = t2.id )
WHERE t1.id_table2 IS NULL

 

 

hmm I think I didn't ask the proper question, I'm really sorry.

 

What i'm trying to do is select every row of 'games_owned_by_avatar' where it is owned by owner_id 17 and not having a secondary key included in games_linked where games_server id = 317.

 

Basically I have a bunch of 'objects/games' that connect to different servers, each game needs to be linked to the server by the owner through a webpage I made. On the web page I list 1) all the games currently linked to the server selected, in this example it is server 317, and 2) all games that are currently not linked to the server by owned by owner_id 17.

 

I dont want to dump the whole table structure here for anonymity reasons but I'm really stuck on this one having a brain fart. It's been awhile since I've done anything in mysql. What is below is def. wrong but any suggestions are helpful.

 

SELECT ga.name,  g.id, g.game_id, g.uuid, g.date, ml.x, ml.y, ml.z, r.region_name, g.name AS game_name
FROM games_owned_by_avatar g,  games ga, regions r, players p, machine_locations ml 
LEFT JOIN  games_linked gl  ON ( gl.server_id = '317' )
WHERE g.owner_id = '17' AND
ga.id = g.game_id AND
ml.game_id = g.id AND
gl.server_id IS NULL AND
ml.region_id = r.id
GROUP BY g.id 
ORDER BY g.date DESC;

[Fog Juice]

something like this

 

SELECT ga.name,  g.id, g.game_id, g.uuid, g.date, ml.x, ml.y, ml.z, r.region_name, g.name as game_name

FROM games_owned_by_avatar g,  games ga, regions r, players p, machine_locations ml, games_linked gl

WHERE g.owner_id = '".cleanup('', $_SESSION['user_id'])."' AND

ga.id = g.game_id AND

    ml.game_id = g.id AND

    (gl.server_id IS NULL OR gl.server_id != '317') AND

ml.region_id = r.id $filter

GROUP BY g.id

ORDER BY g.date DESC;

 

 

but.. one that works. lol

[fenway]

Let's go back to the table1/table2 example... how does you magic number come into play?