Select info from a table that's not inside another table....

[vhkristof]

Hello,

 

I\'ve got 2 tables:

Table1:

id

name

city

 

Table2:

id

level

 

These are not my actual tables, but ...

 

What I want to do is:

Get al the id\'s from Table2 that are NOT in Table1

Something like the MINUS-property in Oracle

 

 

Can anyone help me?

 

Thx!

[shivabharat]

Try this and let me know if this helped you!!

 

select id,name from table_one where id NOT IN (select id from table_two)

[vhkristof]

This is my code, and it gives an error

[php:1:a2640e58e6]<?php

$sql = mysql_query(\"SELECT *

FROM kalender

WHERE ploegid = \'$ploegid\'

AND matchid NOT IN (SELECT matchid FROM wedstrijdverslagen)\")

or die(mysql_error());

?>[/php:1:a2640e58e6]

[shivabharat]

SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);



SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2

WHERE table1.id=table2.id);

 

Try this!!

[vhkristof]

Nope, it ain\'t working :\'(

 

This is my code:

[php:1:8651aac6a3]<?php

$sql = mysql_query(\"SELECT *

FROM kalender

WHERE ploegid = \'$ploegid\'

AND matchid NOT EXISTS (

SELECT verslag.matchid

FROM wedstrijdverslagen verslag, kalender

WHERE kalender.matchid = verslag.matchid

)

\")

or die(mysql_error());

?>[/php:1:8651aac6a3]

 

I also tried removin\' matchid from AND matchid NOT EXISTS ...

 

 

Thx for your reply\'s !

[vhkristof]

I tried it in dozen ways, but it just won\'t work

 

HELP!

[pallevillesen]

EDIT: TESTED IT, WORKS NOW.

 

Because.... SUBSELECTS are not working in mysql 3.23.

(have anybody tried the new 4.0 - should I upgrade?)

 

Anyway this one should be easy though:

 

1. left outer join the two tables with BOTH ids, left join t2 on t1

 

Then it will look like this (assuming id 2 does not exist in table 1, but do exist in table 2):

 

T2.ID T1. ID

1 1

2 NULL

3 3

 

etc.

 

SO:

 

SELECT t2.id as ID2, t1.id as ID1

FROM table2 t2 LEFT OUTER JOIN table1 t1 on t2.id = t1.id

WHERE t1.id IS NULL;

 

 

Example:

mysql> select a.id as aid, b.id as bid from b left outer join a on b.id = a.id where a.id IS NULL;

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

| aid  | bid |

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

| NULL | d   |

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

1 row in set (0.00 sec)

 

 

Try it,

P.

 

ps. If we had a FULL OUTER JOIN, you could get the IDs which were unique in table 1 as well (just the other coloumn). This is possible, described in an earlier post (how to do full outer joins in mysql). - I think v. 4.0 supports this.

[vhkristof]

Thx for your reply, it works!

 

But what does the

WHERE t1.id IS NULL

part do?

[pallevillesen]

It does the magic...

 

When doing an OUTER join, any missing values from the table1.id coloumn is filled with NULL, and that is the rows you\'re interested in...

 

The rows where an table2.ID exist but table1.id does not - the normal join deletes these rows, the outer join fills them up with NULL....

 

Have fun,

 

P.