Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: comparing tables

    Hi,

    I have two tables containing (ahem) lists of mp3 tunes. One is the "master" list (table name "mp3_master") - everything I've got on my home pc. The other (table name "mp3") details everything I've got on my work pc.

    Both tables have an id field, an "artist" field and a "title" field.

    I'd like to simply compare the two tables and return a list of any artist/title combinations that are in the mp3 table (ie: that I have at work) but not in the mp3_master table (ie: that I haven't yet taken home).

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    standard sql solution:

    select id, artist, title from mp3
    except
    select id, artist, title from mp3_master

    microsoft sql/server syntax has minus instead of except, i believe

    if that don't work, do it the old-fashioned way:

    select id, artist, title from mp3
    where id not in (select id from mp3_master)

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Except is a function used in Analysis Services - MDX

    The query

    select id, artist, title from mp3
    where id not in (select id from mp3_master)

    will work fine, however it is a slow way, performance wise. Once a match is found the main query will not stop scanning the sub-query. A faster query is to use the NOT EXISTS

    select id, artist, title from mp3
    where not exists (select * from mp3_master mp3.id = mp3_master.id)

    Once a match is found the search stops
    MCDBA

  4. #4
    Join Date
    Jan 2002
    Posts
    189
    Hmm - maybe I should have added that the id numbers in these tables don't match up.

    select id, artist, title from mp3
    where title not in (select title from mp3_master)

    I used that. As long as I've not got the same tune by different artists, I should be ok.


    select id, artist, title from mp3
    where not exists (select * from mp3_master mp3.id = mp3_master.id)

    I get a "syntax error near "." on that - is that some sort of shorthand for a join?

    Cheers anyway

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't have sql/server to test on, so i'm guessing, but like i said, i think the operator is MINUS

    select artist, title from mp3
    minus
    select artist, title from mp3_master


    if the id numbers don't match up, you don't want to match on id number in the subselect, neither a's way nor mine

    rudy

  6. #6
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    Hi,

    The 'minus' clause certainly won't work in Query Analyzer. There is no syntax even simlar in SQL Server.

    The 'not in' or 'where not exists' are the correct syntax.

    You had some typos in the SQL that failed with the syntax error. It should be:

    select id, artist, title from mp3
    where not exists (select * from mp3_master where mp3_master.mp3.id = mp3_master.id)

    Hope this helps.

    - Andy Abel

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks andy, it wouldn't be the first time sql/server didn't support standard sql

    spudhead, since you can't match on ids, try this --

    select id, artist, title from mp3
    where not exists
    ( select 1 from mp3_master
    where artist = mp3.artist
    and title = mp3.title )

    rudy

  8. #8
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I love reading these forums, to help people and also to read SQLServer bashing. I'm a big fan of SQLServer, so when I read a jab or a bash I have to find out if what is said is true.

    it wouldn't be the first time sql/server didn't support standard sql
    I tried to find out the ANSI standards for SQL and the only thing I could find on MINUS is:
    The MINUS keyword is not ANSI-compliant, the implementation of the MINUS operator is implemented in Oracle.
    So bully for Oracle
    MCDBA

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, i love these syntax discussions too

    actually, the ansi standard operator is EXCEPT

    oracle's support of MINUS is non-standard



    rudy

Posting Permissions

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