How to make LEFT OUTER JOIN except some colums?

Question

I have 3 tables:

First "placement"

Second "user_info"

Third "user_placements"

I want to get all placement data with user infos,

How to do it?

I tried this, but result it not what I expected:

SELECT *, user_placements.id AS user_placements_id, placement.id AS placement_id
FROM placement
LEFT OUTER JOIN user_placements ON placement.id = user_placements.id_placement

Just list those fields between the select and the from that you want to include in the resultset. And also join all 3 tables, not just 2. — Shadow, Jan 31 '17 at 09:29
there is any relation between user_placements and user_info or placement and user_info ? — krishn Patel, Jan 31 '17 at 09:29
@krishnpatel `user_info.id = user_placements.id_user` and `placement.id = user_placements.id_placement` — NickUnuchek, Jan 31 '17 at 09:31

krishn Patel · Accepted Answer · 2017-01-31T09:41:48.590

1

you need to one more join with user info

SELECT placement.*,user_info.id as user_info_id,user_info.name as user_name,user_info.mobile as user_mobile
 FROM placement LEFT OUTER JOIN user_placements ON placement.id = user_placements.id_placement
    LEFT OUTER JOIN user_info ON user_info.id = user_placements.id_user

edited Jan 31 '17 at 09:41

answered Jan 31 '17 at 09:36

krishn Patel

2,579
1
19
30

It's great, but in result there is two columns "id" , one from `placement`, and second from `user_info` – NickUnuchek Jan 31 '17 at 09:39
yes it will be if you want different you should write different way – krishn Patel Jan 31 '17 at 09:39
1

Is any way to use operator "AS" to rename them? – NickUnuchek Jan 31 '17 at 09:41
see I have done for user_info if you want to change for placement you can do it now – krishn Patel Jan 31 '17 at 09:42
What if my user_info will have a lot of fields? Then how to rename "id" field by using operator "AS"? – NickUnuchek Jan 31 '17 at 10:47

score 1 · Answer 2 · answered Jan 31 '17 at 09:37

You are missing the second join:

SELECT *
FROM placement AS p
JOIN user_placements AS up ON p.id = up.id_placement
JOIN user_info AS u ON up.id_user = u.id

Replace the wildcard with the data you want. You will of course get duplicated data with this query.

How to make LEFT OUTER JOIN except some colums?

2 Answers2