Query Help - Join statement

madonnagrace · #1 (**permalink**) 08-19-09, 06:14

hi ,

Pls help with a query where certain fields FROM table 1 is to be selected WHICH are not present in table 2.

Hav tried the general query of "SELECT table1.item_id FROM table1 where table1.item_id NOT IN (SELECT item_id FROM table 2)" but it does not work in the mysql DB whereas the same query worked well with INgRES ...

Pls help if it could work with a JOIN statement and how ...

Thanks
regards
madonna

r937 · #2 (**permalink**) 08-19-09, 06:55

Quote:

Originally Posted by madonnagrace

but it does not work in the mysql DB

oh yes it does

well, it does assuming you are on a recent version, and by recent i mean one that was released in the current millenium

anyone still running 3.23 or 4.0 deserves these types of difficulties

gvee · #3 (**permalink**) 08-19-09, 07:18

The key is an OUTER join

Code:

SELECT table1.item_id
FROM   table1
 LEFT
  JOIN table2
    ON table2.item_id = table1.item_id
WHERE  table2.item_id IS NULL

madonnagrace · #4 (**permalink**) 08-19-09, 08:12

thanks gvee georgee ... and r937 will have a look at your SQl book for future ref if any ...

Internet Services

Web Development

Digital Marketing

Consumer Tech