MySQL if statement conditional join

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.

TELL ME YOUR TABLE SCHEMA AND RELATION between these tables.. you have a lot of syntax error — xkeshav, Oct 03 '11 at 10:58
How can you write a query if you don't know what you want it to do? Please try to describe in words what you want. It will help yourself too. Often you see the solution as soon as you start describing the problem. — GolezTrol, Oct 03 '11 at 10:59
Reading the pseudo-query that has been written, it appears @Akshat wants to join `accounttable` on `peopletable` and `companytable` depending on the value of `account_type`. Fairly straight forward to me. — Romain, Oct 03 '11 at 11:01
i tried to write it as a statement but stackoverflow codified the entire thing — Tarang, Oct 03 '11 at 14:28

Romain · Accepted Answer · 2011-10-03T14:35:36.553

15

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...

edited Oct 03 '11 at 14:35

answered Oct 03 '11 at 11:00

Romain

12,679
3
41
54

Excellent works perfectly, (third line peopletable.id is supposed to be companytable.companyid) very clever way to use that acountype=xx and 'and' together! – Tarang Oct 03 '11 at 14:32
@Akshat fixed the answer to include your note on the typo ;) – Romain Oct 03 '11 at 14:35

score 6 · Answer 2 · answered Oct 03 '11 at 11:01

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

@diEcho : Romain had given fast answer, no ? – Upendra Chaudhari Oct 03 '11 at 11:09 — Upendra Chaudhari, Oct 03 '11 at 11:09

xkeshav · Answer 3 · 2011-10-03T11:23:26.890

0

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'

edited Oct 03 '11 at 11:23

answered Oct 03 '11 at 11:01

xkeshav

53,360
44
177
245

@Upendra did u voted down my answer??? i did not voed down you. see my comment below your answer – xkeshav Oct 03 '11 at 11:30
and i din't voed down you instead i tell your mistake honestly. – xkeshav Oct 03 '11 at 11:36

MySQL if statement conditional join

3 Answers3

Linked