Results 1 to 4 of 4

Thread: [RESOLVED] SQL - Challenge selecting from two tables

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Resolved [RESOLVED] SQL - Challenge selecting from two tables

    Hi
    I have two tables and I need to be able to do the following:
    - show a list of items that are in both tables
    - in addition, show what is in table 1 and not in table 2
    - and finally, show what is table two and not in table 1 .

    So, I need to show what is common and what is different in one query. I use union but I don't get it right

    My query:
    Code:
    --common things
    SELECT 
     NG.[DEVIATION_NUMBER] As [Case ID]                                             
    ,NG.[DEVIATION_HEADLINE]
    ,NG.DV_Owner as Owner
    ,NG.CLASSIFICATION
    ,null AS ActiveStatus
      FROM [DMS_DV_Data_NEW] As NG 
      join [DV_Arena_CaseOverview] DA 
      on NG.DEVIATION_NUMBER = DA.CaseID
      Where NG.Staus In ('Open') 
     And DA.ActiveStatus = 'Yes' 
    
    Union 
    -- in Arana but not in NG
    SELECT distinct
    DA.[CaseID] As [Case ID]                                             
    ,DA.[CaseTitle] As Title
    ,DA.DVowner As Owner
    ,DA.DV_Classification
    ,DA.ActiveStatus AS ActiveStatus
    FROM [DV_Arena_CaseOverview] DA 
    Where  											
    DA.DVstatus In ('Open') And    
    DA.ActiveStatus = 'Yes' And
    DA.CaseType = 'Deviation' And
    DA.[CaseID] not in (Select [DEVIATION_NUMBER] FROM [DMS_DV_Data_NEW]) 
    
    Union 
    -- in NG but not in Arena
    SELECT distinct
     NG.[DEVIATION_NUMBER] As [Case ID]                                             
    ,NG.[DEVIATION_HEADLINE]
    ,NG.DV_Owner as Owner
    ,NG.CLASSIFICATION
    ,null AS ActiveStatus
      FROM [DMS_DV_Data_NEW] As NG 
    Where NG.Staus In ('Open') And 
    NG.[DEVIATION_NUMBER] Not In (Select CaseID FROM [DV_Arena_CaseOverview] Where DVstatus In ('Open') And ActiveStatus = 'Yes' And CaseType = 'Deviation')
    Thanks for any help.
    Last edited by Grand; Feb 9th, 2021 at 10:16 AM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,301

    Re: SQL - Challenge selecting from two tables

    Forget the union for the time being. It is basically a way to combine the results of multiple queries, so concentrate on the individual queries first. Once they are all working individually, then think about using union to combine them. ALWAYS break your problem down into smaller parts and tackle each part in isolation, then combine the partial solutions into one.

    So, when you have done that, which part is not working as expected? Don't say all of them because, while the two parts about records that are in one table but not the other are the same, the other part is different, so you should be posting at least two separate questions for those two different problems.

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL - Challenge selecting from two tables

    1) INNER JOIN
    2) Table1 LEFT JOIN Table2 with ISNULL on Right Hand side (Table2)
    3) Table2 LEFT JOIN Table1 with ISNULL on Right Hand side (Table1)

    EDIT: Or all three together: FULL OUTER JOIN
    Last edited by Zvoni; Feb 10th, 2021 at 02:29 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Re: SQL - Challenge selecting from two tables

    Fixed it. the problem was in the last portion (also a bit in my head, it is messy out there).
    Thank you both again for your guides.

Posting Permissions

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



Click Here to Expand Forum to Full Width