Post reply

Selecting Values In One Table Using Another Table As Filter

  • January 7, 2004 at 1:42 pm

    #83747

    hello in there!

    two tables.

    table1 contains fees and other transaction.  there are no keys, flags or other indicators in the table to differentiate between them.

    table 2 contains transactions and every non-fee transaction in table1.  again, there are no keys, flags or other indicators in the table to differentiate them.

    neither table has matching keys.

    both tables hav a custnr, trxsorc, and date which form a distinct row.

    i wish to SELECT the rows in table1 that are in table2, and then UNSELECT those rows from table1 (which will leave me the fee data)

    this is a SELECT statement, not to modify table data.

    thanks!

  • January 7, 2004 at 1:48 pm

    #489037

    select a.*

     from table1 a

       right outer join table2 b

          on ....

    where a.xx is null (pick some column)

     

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 8, 2004 at 6:30 pm

    #489264

    Hey Steve,

     

    Why did you do a right join instead of Left? I almost allways do Left Joins as I like to have the "Main" table first in the join clause and the "Filter" table come second. Niether way is better I was just curious!

     

    IE:

    -- Select all records from Table1 that don't exist in Table2

    SELECT *

    FROM Table1 a LEFT JOIN Table2 b ON a.Field1 = b.Field2 and...-- add the other linking columns here...

    WHERE B.Field2 IS NULL




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 8, 2004 at 9:29 pm

    #489277

    Right versuses left is nothing more than choice of table reference order and which needs all the data. In this case table 2s data should all be there but those from 1 elminated so only fee data is left.

     

    If this case becuase Steve followed name order for reference order RIGHT join applies, for LEFT you need to have Table 2 first then Table 1 and still do the same thing. 

  • January 8, 2004 at 10:25 pm

    #489281

    Antares,

    I realize that there is no major difference in if you use right or left when properly done. I just found it amusing that he wrote it using a right join where as I would have written it with a left. I would say I use a right join about 1% of the time when doing an outer join. Old habits die hard I guess!




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 12, 2004 at 4:07 am

    #489580

    Oops sorry mistook your question for the wrong intent. I do myself usually right for LEFT JOINS simple because it is easier for me to visualize.

  • January 13, 2004 at 3:17 pm

    #489868

      thanks, all!

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

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