• From: Junsele, Sweden
  • Registered: 2002-01-09
  • Posts: 270

Topic: sql query

I have a small problem here, im not sure if its just because its monday or if it really is tricky but i shouldnt be to hard?

I have 2 tables, the first table has alot of rows and got a uniqe id-number.
the other tables contains one column with some numbers matching table1´s idnumber.

Now i want to select all rows in table 1 that does not have a matching number in table 2.

Like this:

Table 1:
1|bla|mu
2|asd|mu
3|dds|mu
4|ewr|mu
5|iu5|mu
6|kur|mu
7|weu|mu
8|uuu|mu

Table 2:
3
5
7

What i want out of this is:
1|bla|mu
2|asd|mu
4|ewr|mu
6|kur|mu
8|uuu|mu

Table 2 can also be completly empty.

2 Reply by CodeDuck (edited by CodeDuck 2004-10-18 14:34)

  • From: Gothenburg, Sweden
  • Registered: 2002-05-27
  • Posts: 222

Re: sql query

How about:

SELECT table1.*
FROM table1
WHERE NOT table1.id IN (SELECT table2.id FROM table2)
  • From: Junsele, Sweden
  • Registered: 2002-01-09
  • Posts: 270

Re: sql query

CodeDuck wrote:

How about:

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

Yes but there is one little problem, this host runs MySQL 3.23.46 witch does not support subselects =/

  • From: Gothenburg, Sweden
  • Registered: 2002-05-27
  • Posts: 222

Re: sql query

Gribber wrote:

Yes but there is one little problem, this host runs MySQL 3.23.46 witch does not support subselects =/

Try this instead:

SELECT table1.*
FROM table1 
    LEFT OUTER JOIN table2 ON table1.id = table2.id 
WHERE table2.id IS NULL
  • From: Junsele, Sweden
  • Registered: 2002-01-09
  • Posts: 270

Re: sql query

Well, look at that, it really works =)
Danke danke