Rows of tbl1 that arent present in tbl2

anjanesh · #1 (**permalink**) 03-22-06, 14:41

Hi

I got 2 tables and I want rows from tbl1 that ids arent present in tbl2.

Code:

SELECT `id`, `info`
FROM `tbl1`
WHERE `id` <> (SELECT `id` FROM `tbl2`)

I get the error :
#1242 - Subquery returns more than 1 row
Any idea how to make the query work ?

Thanks

gtkuntz · #2 (**permalink**) 03-22-06, 15:12

use
where 'id' not in (select id........)

anjanesh · #3 (**permalink**) 03-22-06, 15:50

Ah...Thanks a lot !

Edit : BTW, Where do I search for this in MySQL 3.x,4.x docs ? Im using ver 5.x on my PC but host has 3.23 and want to know of NOT IN is acceptable in 3.x and 4.x.

benanat · #4 (**permalink**) 03-26-06, 10:02

use left join and is null to get what u want,
like this:

*************
SELECT
ID
FROM
TableA
LEFT JOIN
TableB
ON
(TableA.ID = TableB.ID)
WHERE
(TableB.ID IS NULL)
**********************
this is exectly what u need

healdem · #5 (**permalink**) 03-26-06, 15:49

Quote:

Originally Posted by anjanesh

Ah...Thanks a lot !

Edit : BTW, Where do I search for this in MySQL 3.x,4.x docs ? Im using ver 5.x on my PC but host has 3.23 and want to know of NOT IN is acceptable in 3.x and 4.x.

it would seem sense to run the same version (certainly the same major version) as your ISP. so either develop on a version 3.23 MySQL, or find an ISP which supports version 5

anjanesh · #6 (**permalink**) 06-24-06, 23:16

Quote:

Originally Posted by gtkuntz

use
where 'id' not in (select id........)

Code:

SELECT `ID`, `info` FROM `tbl1` tbl1
WHERE tbl1.`ID` NOT IN (SELECT `ID` FROM `tbl1`)

is returning just 1 row when it should be returning more.
Any idea why ?

Using MySQL 5.0.22

r937 · #7 (**permalink**) 06-25-06, 04:49

Quote:

Originally Posted by anjanesh

Any idea why ?

yes, because you mentioned the wrong table in the subquery

anjanesh · #8 (**permalink**) 06-25-06, 05:30

Code:

SELECT `ID`,`info` FROM `tbl1`
WHERE `ID` NOT IN (SELECT `ID` FROM `tbl2`)

returns 0

r937 · #9 (**permalink**) 06-25-06, 08:04

okay, why don't you try benanat's solution

Code:

select tbl1.id
     , tbl1.info 
  from tbl1
left outer
  join tbl2
    on tbl2.id = tbl1.id 
 where tbl2.id is null

anjanesh · #10 (**permalink**) 06-25-06, 08:13

Sometimes the JOIN statements are taking more than 10 secs to execute.
benanat's solution did work though.

Btw, Im executing all these in phpMyAdmin.

r937 · #11 (**permalink**) 06-25-06, 08:38

make sure that each id column has an index (note: if it's a primary key, it already has an index)

Internet Services

Web Development

Digital Marketing

Consumer Tech