Jump to content


Photo

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


  • Please log in to reply
8 replies to this topic

#1 vhkristof

vhkristof
  • Members
  • PipPip
  • Member
  • 11 posts
  • LocationAchel, Limburg, Belgium

Posted 20 March 2003 - 06:10 PM

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!

#2 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 20 March 2003 - 09:18 PM

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)


Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#3 vhkristof

vhkristof
  • Members
  • PipPip
  • Member
  • 11 posts
  • LocationAchel, Limburg, Belgium

Posted 20 March 2003 - 10:25 PM

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]

#4 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 20 March 2003 - 10:47 PM


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!!
Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#5 vhkristof

vhkristof
  • Members
  • PipPip
  • Member
  • 11 posts
  • LocationAchel, Limburg, Belgium

Posted 20 March 2003 - 10:52 PM

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 !

#6 vhkristof

vhkristof
  • Members
  • PipPip
  • Member
  • 11 posts
  • LocationAchel, Limburg, Belgium

Posted 21 March 2003 - 07:15 AM

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

HELP!

#7 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 21 March 2003 - 08:04 AM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#8 vhkristof

vhkristof
  • Members
  • PipPip
  • Member
  • 11 posts
  • LocationAchel, Limburg, Belgium

Posted 21 March 2003 - 08:36 AM

Thx for your reply, it works!

But what does the

WHERE t1.id IS NULL

part do?

#9 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 21 March 2003 - 09:35 AM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users