Jump to content

Archived

This topic is now archived and is closed to further replies.

vhkristof

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

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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]

Share this post


Link to post
Share on other sites


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!!

Share this post


Link to post
Share on other sites

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 !

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Thx for your reply, it works!

 

But what does the


WHERE t1.id IS NULL

part do?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.