-
Feb 9th, 2021, 10:05 AM
#1
Thread Starter
Fanatic Member
[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.
-
Feb 9th, 2021, 06:57 PM
#2
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.
-
Feb 10th, 2021, 02:25 AM
#3
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
-
Feb 10th, 2021, 05:24 AM
#4
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|