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)
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')
请注意，a、p、c 是完整表名的别名，这可以节省键入时间。
此查询将为连续的 p.* 或 c.* 提供所有 null。 您可以像这样重写选择部分：
SELECT
a.id, a.accounttype
, COALESCE(p.name, c.name) as name
, COALESCE(p.address, c.address) as address
....
FROM .....
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 .....
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'
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'
发布评论
评论（3）
怎么样：
我将连接两个表，这样您将在输出中拥有所有三个表的字段。如果
accounttype != 'person'，则peopletable中的字段将为NULL，而companytable中的字段将为 < code>NULL 其中accounttype != 'company'。您可以在 这里...
What about something like:
I'll join against both tables, so you'll have fields of all three tables in the output. The fields from
peopletablewill beNULLifaccounttype != 'person', and those ofcompanytablewill beNULLwhereaccounttype != 'company'.You can find more on the
LEFT JOINsyntax in places like here...请注意，a、p、c 是完整表名的别名，这可以节省键入时间。
此查询将为连续的 p.* 或 c.* 提供所有
null。您可以像这样重写选择部分：
请参阅： http://dev.mysql .com/doc/refman/5.0/en/comparison-operators.html#function_coalesce
Note that a,p,c are aliases for the full tablenames, this saves on typing.
This query will give all
nullfor either p.* or c.* in a row.You can rewrite the select part like so:
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 )