Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    18

    Unanswered: differenct between 2 select statments

    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);

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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)

  3. #3
    Join Date
    Oct 2003
    Posts
    357
    Hi, try this
    Code:
    select  t1.documentpackageid -t2.documentpackageid from table1 t1, 
    table2 t2  where t1.xxx=94 and t2.yyy<>10
    Madhivanan

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    Last edited by r937; 12-16-04 at 10:12.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •