Create 2 new columns from a query

Question

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.

score 1 · Accepted Answer · edited Apr 19 '13 at 10:47

1

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

edited Apr 19 '13 at 10:47

Himanshu

31,810
31
111
133

answered Apr 19 '13 at 10:43

Sen Jacob

3,384
3
35
61

This worked well for me. It produced exactly what i required. thank you well much – user1367386 Apr 19 '13 at 12:30
I'm happy to know it helped. Please mark this post as answer. – Sen Jacob Apr 22 '13 at 04:10

score 1 · Answer 2 · answered Apr 19 '13 at 10:48

1

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

answered Apr 19 '13 at 10:48

TechDo

18,398
3
51
64

Will subquery make it run slow? – Sen Jacob Apr 19 '13 at 10:49
1

[Join Vs SubQuery](http://stackoverflow.com/questions/2577174/join-vs-subquery) - "In most cases JOINs are faster than sub-queries and it is very rare for a sub-query to be faster." – Sen Jacob Apr 19 '13 at 11:03

score 0 · Answer 3 · answered Apr 19 '13 at 10:42

0

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.

answered Apr 19 '13 at 10:42

Muhammad Hani

8,476
5
29
44

the INNER JOIN will eliminate the one that has the null value particularly the 2nd row in his/her example. – Edper Apr 19 '13 at 10:56

Create 2 new columns from a query

3 Answers3

See this SQLFiddle