CODEWITHSUNDEEP
sqlsql-serverjoin
Alisher Narzullaev
Alisher Narzullaev

Reputation: 11

Joining on two columns

I have two tables: table_1 :

  NAME VALUES_WANTED
  A-1  value_1
  A-1  value_2
  A-1  value_3
  A-1  value_4
  A-1  value_5
  A-2  value_1
  A-2  value_2
  A-2  value_3
  A-2  value_4
  A-2  value_5

and table_2:

  NAME VALUES   TAGS
  A-1  value_1  a
  A-1  value_2  aa
  A-1  value_3  s
  A-1  value_4  b
  A-1  value_y  e
  A-1  value_x  sd
  A-2  value_1  g
  A-2  value_2  fe
  A-2  value_3  k
  A-2  value_5  m

I want to get the following table:

  NAME VALUES_WANTED   TAGS
  A-1  value_1         a
  A-1  value_2         aa
  A-1  value_3         s
  A-1  value_4         b
  A-1  value_5         Null
  A-2  value_1         g
  A-2  value_2         fe
  A-2  value_3         k
  A-2  value_4         Null
  A-2  value_5         m

Basically, it is smth like lookup of TAGS for VALUES_WANTED. If the VALUES_WANTED is missing in table_2, it should return Null for its TAGS.

I know I need to join on NAME and VALUES. Have been trying different ways of JOIN like the following, but still failing...

SELECT a.NAME, a.VALUES, a.TAGS, s.VALUES_WANTED
FROM table_2 a


RIGHT JOIN table_1 s 
ON a.NAME = s.NAME 
AND a.VALUES = s.VALUES_WANTED

Any help is highly appreciated.

Upvotes: 0

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You seem to want a left join on the two columns:

SELECT t1.NAME, t1.VALUES_WANTED, t2.TAGS
FROM table_1 t1 LEFT JOIN
     table_2 t2 
     ON t1.NAME = t2.NAME AND t1.VALUES_WANTED = t2.VALUES;

In a LEFT JOIN, all rows from the first table are kept along with matching rows from the second table. That appears to be what you want.

You can also write this using a RIGHT JOIN, but I don't recommend that -- most people find it easier to read the FROM clause when they know what rows are needed.

Your version is close, but you are not selecting the right columns. You have a.Name in the result set, but that will be NULL when there is not a match.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32001

use left join

SELECT a.NAME, a.VALUES_WANTED, b.TAGS
FROM table_1 a
left JOIN table_2 b 
ON a.NAME = b.NAME 
AND a.VALUES = b.VALUES_WANTED

Upvotes: 1

Related Questions