MySQL if statement conditional join [closed]

Closed. This question needs details or clarity. It is not currently accepting answers.

---

Want to improve th开发者_Python百科is question? Add details and clarify the problem by editing this post.

Closed 2 years ago.

Improve this question

I am trying to do something like

SELECT * from accounttable, peopletable, companytable 
WHERE if accounttable.account_type = company 
JOIN companytable 
WHERE companytable.id = accounttable.company_id 
ELSE IF accounttable.account_type = = person 
JOIN peopletable 
WHERE peopletable.id = accounttable.person_id

I'm sorry its a bit sqlenglish, but I really don't know how to write it out.

---

What about something like:

SELECT * 
from accounttable
left join peopletable ON (accounttype = 'person' AND peopletable.id = accounttable.person_id)
left join companytable ON (accounttype = 'company' AND companytable.id = accounttable.company_id)

I'll join against both tables, so you'll have fields of all three tables in the output. The fields from peopletable will be NULL if accounttype != 'person', and those of companytable will be NULL where accounttype != 'company'.

You can find more on the LEFT JOIN syntax in places like here...

---

SELECT a.*, p.*, c.* from accounttable a
LEFT JOIN peopletable p ON (a.person_id = p.id AND a.account_type = 'person')
LEFT JOIN companytable c ON (a.company_id = c.id AND a.account_type = 'company')

Note that a,p,c are aliases for the full tablenames, this saves on typing.

This query will give all null for either p.* or c.* in a row.
You can rewrite the select part like so:

SELECT 
  a.id, a.accounttype
  , COALESCE(p.name, c.name) as name
  , COALESCE(p.address, c.address) as address
....
FROM .....

See: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

---

IT MUST BE like below ( as far as i understand )

SELECT a.*,p.*,c.* 
FROM accounttable a
LEFT JOIN companytable c ON c.id = a.company_id AND a.account_type = 'company'
LEFT JOIN peopletable p ON p.id = a.person_id AND  a.account_type = 'person'