Sql-server – Combining Two SQL Result Sets by Single Column but want to show rows where no match

sql server

So my first query is the following:

select Equipment_Reserved.idEquipment_Reserved,Equipment.Name,Equipment_Reserved.Reason, convert(varchar(20),Equipment_Reserved.ReservedStart,106) as [Start Date], convert(varchar(20),Equipment_Reserved.ReservedEnd,106) as [End Date]
from Equipment, Studies, Equipment_Reserved
where Studies.Study = 'MR18-42'
and Equipment.idEquipment = Equipment_Reserved.Equipment_idEquipment 
and Studies.idStudies = Equipment_Reserved.Studies_idStudies
and Equipment.Type = 'Reactor'
order by Name ASC, Reason DESC;

And results in the following:

idEquipment_Reserved    Name    Reason      Start Date  End Date
1                       813     Seed        10 Aug 2018 13 Aug 2018
4                       813     Production  14 Aug 2018 31 Aug 2018
2                       814     Seed        10 Aug 2018 13 Aug 2018
5                       814     Production  14 Aug 2018 31 Aug 2018
3                       815     Seed        10 Aug 2018 13 Aug 2018
6                       815     Production  14 Aug 2018 31 Aug 2018
7                       816     Production  14 Aug 2018 31 Aug 2018
8                       821     Production  14 Aug 2018 31 Aug 2018
9                       822     Production  14 Aug 2018 31 Aug 2018
10                      823     Production  14 Aug 2018 31 Aug 2018
11                      824     Production  14 Aug 2018 31 Aug 2018

The second query is the following:

select Equipment_Reserved.Equipment_Attached_To, Equipment.Family, Equipment.Name
from Equipment, Studies, Equipment_Reserved
where Studies.Study = 'MR18-42'
and Equipment.idEquipment = Equipment_Reserved.Equipment_idEquipment 
and Studies.idStudies = Equipment_Reserved.Studies_idStudies
and Equipment.Type = 'Raman'
order by Name ASC, Reason DESC

And results are the following:

Equipment_Attached_To   Family  Name
4                       Raman 3 Probe 1
8                       Raman 2 Probe 1
9                       Raman 2 Probe 2
5                       Raman 3 Probe 2
6                       Raman 3 Probe 3
10                      Raman 2 Probe 3
11                      Raman 2 Probe 4
7                       Raman 3 Probe 4

So, I can combine them in the following manner:

SELECT x.Name, x.Reason, x.[Start Date], x.[End Date], y.Family as [Raman], y.Name as [Probe]
FROM
(select Equipment_Reserved.idEquipment_Reserved,Equipment.Name,Equipment_Reserved.Reason, convert(varchar(20),Equipment_Reserved.ReservedStart,106) as [Start Date], convert(varchar(20),Equipment_Reserved.ReservedEnd,106) as [End Date]
from Equipment, Studies, Equipment_Reserved
where Studies.Study = 'MR18-42'
and Equipment.idEquipment = Equipment_Reserved.Equipment_idEquipment
and Studies.idStudies = Equipment_Reserved.Studies_idStudies
and Equipment.Type = 'Reactor') as x,
(select Equipment_Reserved.Equipment_Attached_To, Equipment.Family, Equipment.Name
    from Equipment, Studies, Equipment_Reserved
    where Studies.Study = 'MR18-42'
    and Equipment.idEquipment = Equipment_Reserved.Equipment_idEquipment
and Studies.idStudies = Equipment_Reserved.Studies_idStudies
and Equipment.Type = 'Raman') as y
WHERE x.idEquipment_Reserved = y.Equipment_Attached_To

And I get the following:

Name    Reason      Start Date  End Date    Raman   Probe
813     Production  14 Aug 2018 31 Aug 2018 Raman 3 Probe 1
814     Production  14 Aug 2018 31 Aug 2018 Raman 3 Probe 2
815     Production  14 Aug 2018 31 Aug 2018 Raman 3 Probe 3
816     Production  14 Aug 2018 31 Aug 2018 Raman 3 Probe 4
821     Production  14 Aug 2018 31 Aug 2018 Raman 2 Probe 1
822     Production  14 Aug 2018 31 Aug 2018 Raman 2 Probe 2
823     Production  14 Aug 2018 31 Aug 2018 Raman 2 Probe 3
824     Production  14 Aug 2018 31 Aug 2018 Raman 2 Probe 4

The problem is that I lost all rows where Reason = 'Seed' because those rows do not have any information in the second query. Is there a way that I can change the combined Query so the results look like this:

Name    Reason      Start Date  End Date    Raman   Probe
813     Seed        14 Aug 2018 31 Aug 2018 
813     Production  14 Aug 2018 31 Aug 2018 Raman 3 Probe 1
814     Seed        14 Aug 2018 31 Aug 2018 
814     Production  14 Aug 2018 31 Aug 2018 Raman 3 Probe 2
815     Seed        14 Aug 2018 31 Aug 2018 
815     Production  14 Aug 2018 31 Aug 2018 Raman 3 Probe 3
816     Production  14 Aug 2018 31 Aug 2018 Raman 3 Probe 4
821     Production  14 Aug 2018 31 Aug 2018 Raman 2 Probe 1
822     Production  14 Aug 2018 31 Aug 2018 Raman 2 Probe 2
823     Production  14 Aug 2018 31 Aug 2018 Raman 2 Probe 3
824     Production  14 Aug 2018 31 Aug 2018 Raman 2 Probe 4

Thank you so much for your help!

Best Answer

If you want ALL rows from the x table regardless of a match on the y table, you'll have to use a LEFT JOIN. Try something like this:

SELECT x.Name, x.Reason, x.[Start Date], x.[End Date], y.Family as [Raman], y.Name as [Probe]
FROM
(select Equipment_Reserved.idEquipment_Reserved,Equipment.Name,Equipment_Reserved.Reason, convert(varchar(20),Equipment_Reserved.ReservedStart,106) as [Start Date], convert(varchar(20),Equipment_Reserved.ReservedEnd,106) as [End Date]
from Equipment, Studies, Equipment_Reserved
where Studies.Study = 'MR18-42'
and Equipment.idEquipment = Equipment_Reserved.Equipment_idEquipment
and Studies.idStudies = Equipment_Reserved.Studies_idStudies
and Equipment.Type = 'Reactor') as x LEFT JOIN
(select Equipment_Reserved.Equipment_Attached_To, Equipment.Family, Equipment.Name
    from Equipment, Studies, Equipment_Reserved
    where Studies.Study = 'MR18-42'
    and Equipment.idEquipment = Equipment_Reserved.Equipment_idEquipment
and Studies.idStudies = Equipment_Reserved.Studies_idStudies
and Equipment.Type = 'Raman') as y
ON x.idEquipment_Reserved = y.Equipment_Attached_To
Related Question