1

I have two tables 

TABLE1

ID T3_ID PERIOD AGENT
1   1    2016   12
2   2    2016   13
3   3    2016   12
4   4    2015   12

and

TABLE2

ID T3_ID PERIOD PRODUCT 
1   1    2016   'PRODUCT1'
2   3    2016   'PRODUCT2'
3   3    2015   'PRODUCT2'

I need to get all rows from both tables that match certain conditions and joining by T3_ID field. But in the case that in Table2 there isn't a matching row in Table2 for any T3_ID record in Table1, showing it anyway. The expected result in this example would be:

 EXPECTED RESULT

ID T3_ID PERIOD AGENT PRODUCT
1   1    2016   12    'PRODUCT1'
2   2    2016   13    ''
3   3    2016   12    'PRODUCT2'

I already built a query, it works and shows expected results, but I wonder if there's a better way of doing this.

SELECT T1.*, '' AS PRODUCT FROM TABLE1 T1
WHERE T1.PERIOD='2016' AND T1.T3_ID NOT IN (SELECT T2.T3_ID FROM TABLE2 T2 WHERE T2.PERIOD='2016')
UNION
SELECT T1.*, T2.PRODUCT AS PRODUCT FROM TABLE1 T1
LEFT JOIN TABLE2 T2
ON T1.T3_ID=T2.T3_ID
YanetP1988
  • 1,346
  • 3
  • 18
  • 43
  • You can use an OUTER JOIN it retrieves you the coincident and non coincident values of tables. I leave you a link explaining better the differences between OUTER JOIN and INNER JOIN: https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join – eduCan Jun 28 '18 at 14:12
  • Try it, and you'll see that it doesn't work with outer join – YanetP1988 Jun 29 '18 at 13:54
  • Somebody please!!!! – YanetP1988 Jun 29 '18 at 18:52

1 Answers1

2

IF you are using SQL Server

SELECT T1.*, ISNULL(T2.PRODUCT,'') AS PRODUCT FROM TABLE1 T1
LEFT JOIN TABLE2 T2
ON T1.T3_ID=T2.T3_ID
WHERE T1.PERIOD='2016'
Shikhar Arora
  • 886
  • 1
  • 9
  • 14
  • I have to include T2.period='2016' to restrict results to this year. Your solution still doesn't show all matched records from Table1 – YanetP1988 Jun 28 '18 at 17:03
  • The problem is not that a row Product field is null in Table2, the problem is that for a specific T3_ID in Table1 there is no matching T3_ID in Table2, so the record is not retrieved. So checking for NULL doesn't work – YanetP1988 Jun 28 '18 at 17:07