Jump to content

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


vhkristof

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!

Link to comment
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]

Link to comment
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 !

Link to comment
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.

Link to comment
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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.