differenct between 2 select statments

chitech · #1 (**permalink**) 12-16-04, 05:08

I have 2 table (table1 & table2) where both contain documentpackageid column. In the first table I have a select:

SELECT documentpackageid FROM table1 WHERE xxx=94

The second table:

SELECT documentpackageid FROM table2 WHERE yyy<>10

Now I want the different between that 2 select statement. So that I have all documentpackageid from the first select - documentpakcageid from the second select

I have tried with:
SELECT documentpackageid FROM (SELECT documentpackageid FROM table1 WHERE xxx=94) AS foo WHERE foo.documentpackageoid !IN (SELECT documentpackageid FROM table2 WHERE yyy<>10);

andrewst · #2 (**permalink**) 12-16-04, 05:58

On Oracle the answer is:

Code:

SELECT documentpackageid FROM table1 WHERE xxx=94
MINUS
SELECT documentpackageid FROM table2 WHERE yyy<>10

Some DBMSs have "EXCEPT" instead of "MINUS".

If your DBMS has neither you can do this:

Code:

SELECT documentpackageid FROM table1 WHERE xxx=94
AND NOT EXISTS
( SELECT null FROM table2 WHERE yyy<>10
 AND table2.documentpackageid = table2.documentpackageid)

Madhivanan · #3 (**permalink**) 12-16-04, 08:33

Hi, try this

Code:

select  t1.documentpackageid -t2.documentpackageid from table1 t1, 
table2 t2  where t1.xxx=94 and t2.yyy<>10

Madhivanan

r937 · #4 (**permalink**) 12-16-04, 09:09

tony, mysql has neither EXCEPT nor MINUS

and versions prior to 4.1 don't even have subselects!!

here is another solution to this problem --

Code:

select table1.documentpackageid 
  from table1 
left outer
  join table2
    on table1.documentpackageid
     = table2.documentpackageid
   and table2.yyy <> 10  
 where table1.xxx = 94
   and table2.documentpackageid is null

Internet Services

Web Development

Digital Marketing

Consumer Tech