CODEWITHSUNDEEP
sqlsql-serverjoinsql-server-2012left-join
pjustindaryll
pjustindaryll

Reputation: 377

LEFT JOIN 2 columns within the same table if the same value is available in both column

I have this table. What I want to achieve is a LEFT JOIN to only work if 2 values within the same column is present.

For example, LEFT JOIN will only work on EA-AC-A060047 since it's both available for credit and debit columns. And so on.

pic1

This is what I'm trying to achieve.

pic2

This is what I got so far, no luck.

SELECT 
    t1.referenceno, t2.credit, t3.debit 
FROM
    (SELECT 
         referenceno, credit, debit, employeeidno 
     FROM
         earningsamendment 
     WHERE 
         employeeidno = 'D0080000010') AS t1
LEFT JOIN
    (SELECT 
         referenceno, credit, debit, employeeidno 
     FROM
         earningsamendment 
     WHERE
         employeeidno = 'D0080000010') AS t2 ON t2.referenceno = t1.referenceno
LEFT JOIN
    (SELECT 
         referenceno, credit, debit, employeeidno 
     FROM
         earningsamendment 
     WHERE
         employeeidno = 'D0080000010') AS t3 ON t3.referenceno = t1.referenceno
WHERE
    t2.referenceno = t1.referenceno 
    AND t3.referenceno = t1.referenceno
ORDER BY  
    t1.referenceno

Upvotes: 0

Views: 919

Answers (2)

Excelnoobster
Excelnoobster

Reputation: 129

If you want a left join to only work when two values are present - ie you want it to not return anything if not the case, it sounds like an inner join. So start with distinct reference numbers/employee numbers. The assumption is that there will not be more than one credit and not more than one debit...

SELECT * from

( SELECT DISTINCT referenceno, employeeidno from earningsamendment) as t1

INNER JOIN

( SELECT referenceno,employeeidno,

  (SELECT credit from earningsamendment e3
     where e3.employeeidno = e2.employeeidno and
           e3.referenceno = e2.referenceno and
           isnull(e3.credit ,0) <> 0  ) as credit,


  (SELECT debit from earningsamendment e3
     where e3.employeeidno = e2.employeeidno and
           e3.referenceno = e2.referenceno and
           isnull(e3.debit ,0) <> 0  ) as debit
  FROM earningsamendment e2
  GROUP BY referenceno,employeeidno

) as t2

ON t2.referenceno=t1.referenceno and t2.employeeidno = t1.employeeidno

WHERE NOT t2.debit IS NULL AND NOT t2.credit IS NULL

Upvotes: 1

DarkRob
DarkRob

Reputation: 3833

You can easily achieve this by using group by and 'max`, if you have only 1 row each for credit and debit column.

   select referenceno, max(credit) as Credit, max(debit) as Debit , employeeidno 
   from earningsamendment group by referenceno, employeeidno

This above query give the result as expected posted in above question.

Now if you want to get the record only if both credit and debit is present.

   ; with cte as (
   select referenceno, max(credit) as Credit, max(debit) as Debit , employeeidno 
   from earningsamendment group by referenceno, employeeidno 
   )
   select * from cte where Credit>0 and Debit>0

Upvotes: 1

Related Questions