0

I have a table that can store different messurement values with type as associated column to store measurement type.

following Is a sample table with data

enter image description here

I am able to self join the table to get ht and wt values based on date. My requirement is, I also need wt values of all dates even if ht for that date is not there in the table.

Sample result

enter image description here

EDIT: I tried below query but it is returning only first row.

SELECT 
  ta.uid, ta.value as 'wt', lta.value as 'ht', ta.date,ta.id as lftid, lta.id as rtid FROM 
  [test].[dbo].[tbl2] ta
     LEFT JOIN [test].[dbo].[tbl2] Lta 
        ON ta.[date] = Lta.[date]
       AND ta.[uid] = 11 WHERE 
      ta.type = 'wt'
  AND Lta.type ='ht'
Ankith
  • 145
  • 2
  • 12

2 Answers2

1

With just a few changes to your query it should work:

SELECT 
  ta.uid, ta.value as 'wt', lta.value as 'ht', ta.date,ta.id as lftid, lta.id as rtid FROM 
  [test].[dbo].[tbl2] ta
     LEFT JOIN [test].[dbo].[tbl2] Lta 
        ON ta.[date] = Lta.[date]
       AND ta.[uid] = 11  and ta.type<>Lta.type
       WHERE 
      ta.type = 'wt'
  AND isnull(Lta.type,'ht') ='ht'
Jayvee
  • 10,670
  • 3
  • 29
  • 40
0

The fact that you are refering to the right alias in the where clause effectively changes your left join to an inner join.
Read this answer to find out why.
To avoid that, move the conditions in the where clause to the on clause:

SELECT ta.uid, 
       ta.value as 'wt', 
       lta.value as 'ht', 
       ta.date,
       ta.id as lftid, 
       lta.id as rtid 
FROM [test].[dbo].[tbl2] ta
LEFT JOIN [test].[dbo].[tbl2] Lta 
ON ta.[date] = Lta.[date]
AND ta.type = 'wt'
AND Lta.type ='ht'           
WHERE ta.[uid] = 11
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • It isn't giving the desired result. Instead it is creating more than expected rows with all the mvalues in wt column. – Ankith Jun 27 '17 at 10:27