CODEWITHSUNDEEP
sqlsql-serverjoin
user1367386
user1367386

Reputation: 31

Create 2 new columns from a query

I am battling with joins and inner joins.

I have 2 tables that look like this:

USERS

-----------------------------------------
ID | fname | lname | div_1_id | div_2_id
-----------------------------------------
1    paul    smith     1         2
2    john    lip       2        null
3    kim     long      1         4

DIVISIONS

------------------
ID | name
------------------
1    estate
2    litigation
3    property
4    civil

DESIRED RESULT (sql query)

--------------------------------------------------
user.ID | fname | lname | div_1_name | div_2_name
--------------------------------------------------
  1        paul    smith     estate       litigation
  2        john    lip       litigation   
  3        kim     long      estate       civil

I would like to create a new table from a MS sql query that looks like the above.

Upvotes: 0

Views: 85

Answers (3)

TechDo
TechDo

Reputation: 18649

Try using sub-query:

select a.ID, a.fname, a.lname,
    (select name from DIVISIONS b where b.id=a.div_1_id) div_1_name,
    (select name from DIVISIONS b where b.id=a.div_2_id) div_2_name
from 
USERS a

Upvotes: 1

Sen Jacob
Sen Jacob

Reputation: 3442

Use LEFT JOIN for this:

SELECT u.ID, u.fname, u.lname
      , d1.name as div_1_name
      , d2.name as div_2_name 
FROM USERS u 
LEFT JOIN DIVISIONS d1 ON u.div_1_id = d1.ID 
LEFT JOIN DIVISIONS d2 ON u.div_2_id = d2.ID

See this SQLFiddle

Upvotes: 1

Muhammad Hani
Muhammad Hani

Reputation: 8664

Use INNER JOIN

SELECT Users.ID, Users.fname, Users.lname, Divisions.name,Div.name
FROM Users INNER JOIN Divisions ON Users.div_id_1 = Divisions.ID
INNER JOIN Divisions Div ON Users.div_id_1 = Div.ID -- Second join with Divisions Table with Alias.

Upvotes: 0

Related Questions