Post reply

Compare two tables and find unmatch rows

  • September 25, 2007 at 3:48 pm

    #118724

    I have student and account table with columns id, course, number, class. As I'm novice in querying I need sample how to join these tables and find unmatched rows.

    Thanks in advance

  • September 25, 2007 at 4:00 pm

    #736177

    As a novice you should start from reading manuals.

    Open BOL ("help" in SQL Server), find topic "FROM clause (described)" and read how to use joins.

    It has good definitions, explanations, examples, everything you need for a good start.

    _____________
    Code for TallyGenerator

  • September 25, 2007 at 9:34 pm

    #736226

    I agree... now's the perfect time to begin exploring Books Online... and when someone gives you a hint like what Serqiy did, don't just stop at the hint... look for other things in the Books Online pages that you find. For example, the FROM clause page is going to talk about JOINs... you should also lookup JOINs and see what might serve you...

    Yeah... you could just get the answer from a forum, but then you also wouldn't be learning quite as much for the next problem...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 25, 2007 at 9:38 pm

    #736229

    wouldn't be learning quite as much for the next problem

    Strike that and insert ­­­>> wouldn't be learning at all for the next problem.

  • September 26, 2007 at 12:52 pm

    #736508

    There is a BEGINNERS section for these kind of questions.


    * Noel

  • September 26, 2007 at 1:15 pm

    #736510

    [p]I just felt compelled to answer this as I needed to try out various ways of getting code into the new forum, so I could alter the Simple-Talk Prettifier to do it. No dice, I can't get the IFCodes to behave themselves. Anyway, this is as far as I got! Steve and Steve have some work to do![/p]

    [font="courier new"]CREATE TABLE #FirstTable

    (PersonsName VARCHAR(80),

    favouriteSong VARCHAR(80))

    CREATE TABLE #SecondTable

    (PersonsName VARCHAR(80),

    favouriteSong VARCHAR(80))

    INSERT INTO #firstTable(personsName, FavouriteSong)

    SELECT 'Bill', 'I did it my way'

    UNION SELECT 'Bob', 'Devil Woman'

    UNION SELECT 'Gerhard', 'smoke gets in your eyes'

    UNION SELECT 'Dave', 'That Old black magic'

    UNION SELECT 'Jacob', 'abide with me'

    UNION SELECT 'Gertie', 'I''m Gertie the girl with the gong'

    UNION SELECT 'Anne', 'God save the queen'

    UNION SELECT 'Kishor', 'Pop goes the weasel'

    UNION SELECT 'Isobel', 'Anyone who had a heart'

    INSERT INTO #SecondTable(personsName, FavouriteSong)

    SELECT personsName, FavouriteSong FROM #firstTable

    INSERT INTO #firstTable(personsName, FavouriteSong)

    SELECT 'Ray', 'Waterloo sunset'

    UNION SELECT 'Sacha', 'I''ts a mans mans world'

    UNION SELECT 'Dave', 'I like to teach the world to sing'

    INSERT INTO #SecondTable(personsName, FavouriteSong)

    SELECT 'Donna', 'I am the walrus'

    UNION SELECT 'Blitzen', 'boom bang a bang'

    SELECT 'only In First Table',

    #firstTable.personsName,

    #firstTable.FavouriteSong

    FROM #firstTable LEFT [/color]OUTER JOIN #SecondTable

    ON #firstTable.personsName=#secondTable.personsName

    AND #firstTable.FavouriteSong=#SecondTable.FavouriteSong

    WHERE #SecondTable.FavouriteSong IS NULL

    SELECT 'only In Second Table',

    #SecondTable.personsName,

    #SecondTable.FavouriteSong

    FROM #firstTable RIGHT [/color]OUTER JOIN #SecondTable

    ON #firstTable.personsName=#secondTable.personsName

    AND #firstTable.FavouriteSong=#SecondTable.FavouriteSong

    WHERE #firstTable.FavouriteSong IS NULL

    SELECT 'has two favourites', personsName FROM (

    SELECT personsName, favouriteSong FROM #firstTable

    UNION ALL

    SELECT personsName, favouriteSong FROM #SecondTable)f

    GROUP BY personsname

    HAVING MAX(FavouriteSong)<> MIN(FavouriteSong)[/font]

    Best wishes,
    Phil Factor

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply