SQL Combine 2 Select Statements to get data from both rows

I have a table that contains 2 rows per record. the initial record and then the record when its ended they contain the same information apart from one column on the second row has the completed location.

eg 1st row 
Id, New ID, RecordType, Customer Name, Customer Address, Created,   Closed,   location 
1 , Null,   Initial,     John Smith,    1, long lane,   01/05/2019, 10/05/2019, Office
2nd row
Id, New ID, RecordType,  Customer Name, Customer Address, Created, Closed,    Location
2 , 1,     Completed,     John Smith,    1, long lane,   01/05/2019, 10/05/2019, Field

I want to report on the 1st row but I want the location from the second row, the second row links with the 1st row via the ID, and New ID field.

I’ve tried to use a CTE Query but the results returned are less than the results returned when just querying the 1st row.

WITH group1 AS (select * from a where closed is not null),
group2 AS (select location from a where closed is not null)
SELECT *
  FROM group1
  JOIN group2 ON group1.ID = group2.NewID ;

I hope this makes sense, just want some advice on how to join these 2 rows

Answer

it seems you need left join

WITH group1 AS (select * from a where closed is not null),
group2 AS (select location from a where closed is not null)
SELECT group1.*,group2.location
  FROM group1
  left JOIN group2 ON group1.ID = group2.NewID

even you dont need cte you can use selft join

select t1.*,t2.location
from a t1 left join a t2 on t1.ID = t2.NewID
where t1.closed is not null

Advertisement

Answer