Post reply

select column1 of table1 that doesnot contain in column1 of table 2

  • March 21, 2011 at 1:57 pm

    #233509

    Need to retrieve the records from Table User where User_ID doesnot exist in User_Role Table

    Tried this but doesnt work :

    select .USER_ID, USER_NM,.LAST_UPDT_BY_NM ,.LAST_UPDT_TS from dbo.

    join USER_ROLE on .USER_ID=USER_ROLE.USER_ID

    where ACTIVE_IN='1' and ( .USER_ID != [USER_ROLE].USER_ID)

    Can Anyone fix the and part

  • March 21, 2011 at 2:02 pm

    #1301560

    that's the difference between a JOIN(INNER JOIN) and a LEFT OUTER JOIN

    inner joins only show matches, where OUTER JOINS (LEFT and RIGHT) can show you where something doesn't match, if you add a test in the WHERE conditions:

    SELECT

    .USER_ID,

    USER_NM,

    .LAST_UPDT_BY_NM ,

    .LAST_UPDT_TS

    from dbo.

    LEFT OUTER join USER_ROLE

    ON .USER_ID=USER_ROLE.USER_ID

    where ACTIVE_IN='1'

    AND USER_ROLE.USER_ID IS NULL

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 21, 2011 at 2:03 pm

    #1301561

    You can also utilize a "NOT IN" in the where clause.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • March 21, 2011 at 2:10 pm

    #1301565

    Thank You

  • March 21, 2011 at 2:26 pm

    #1301584

    Or a NOT EXISTS

    http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

    http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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