如何根据tbl_users的vendor_type字段关联查询对应供应商信息？

我需要编写SQL查询，列出所有用户及其对应的供应商信息。tbl_users表的vendor_type字段值仅为company或agency，vendor_id字段会根据vendor_type的值存储对应的company_id或agency_id：

• 当vendor_type为company时，vendor_id对应tbl_company的company_id，需查询该公司的信息
• 当vendor_type为agency时，vendor_id对应tbl_agency的agency_id，需查询该机构的信息

示例 #

• 示例1：user_id=1的vendor_type为company，vendor_id=2，需从tbl_company中取出company_id=2的供应商数据
• 示例2：user_id=2的vendor_type为agency，vendor_id=8，需从tbl_agency中取出agency_id=8的供应商数据

表结构及数据 #

tbl_users
+----------+----------+-------------+-----------+
| user_id  | username | vendor_type | vendor_id |
+----------+----------+-------------+-----------+
|        1 | Vicky RT |  company    |   2       |
|        2 | Adam Sm  |  agency     |   8       |
|        3 | Shane JK |  company    |   5       |
+----------+----------+-------------+-----------+

tbl_company
+------------+-------------+---------------+------------+
| company_id | name        | contact_number| reg_date   |
+------------+-------------+---------------+------------+
|        1   | ABC Pvt Ltd | 0123456789    | 2020-10-21 |
|        2   | XYZ Pvt Ltd | 1234567890    | 2008-08-15 |
+------------+-------------+---------------+------------+

tbl_agency
+-----------+-------------+---------------+--------------+
| agency_id | name        | licence_number| logo         |
+-----------+-------------+---------------+--------------+
|        8  | ABC Agency  | O3BD3OU6FG    | abc-3463.png |
|        9  | XYZ Agency  | UDBD3N5O5W    | xyz-3463.png |
+-----------+-------------+---------------+--------------+

我尝试的查询（未得到预期结果） #

SELECT user_id, username, vendor_type, vendor_id
CASE 
    WHEN vendor_type='company' THEN (SELECT company_id, name, contact_number, reg_date 
       FROM tbl_company WHERE tbl_company.company_id=tbl_users.vendor_id)
    WHEN vendor_type='agency' THEN (SELECT agency_id, name, licence_number, logo 
       FROM tbl_agency WHERE tbl_agency.agency_id=tbl_users.vendor_id)
    ELSE 'null'
END AS vendor_info
FROM `tbl_users` 

问题分析与解决方案 #

你的查询错误在于：CASE语句只能返回单个值，但你试图在分支中返回多列数据，这不符合SQL语法规则。下面提供两种可行的解决方案：

方案1：使用UNION ALL拆分类型查询

分别处理company和agency两种类型的用户，再合并结果：

SELECT 
    u.user_id,
    u.username,
    u.vendor_type,
    u.vendor_id,
    c.company_id AS vendor_detail_id,
    c.name AS vendor_name,
    c.contact_number AS contact_info,
    c.reg_date AS extra_info
FROM tbl_users u
LEFT JOIN tbl_company c ON u.vendor_type = 'company' AND u.vendor_id = c.company_id
WHERE u.vendor_type = 'company'

UNION ALL

SELECT 
    u.user_id,
    u.username,
    u.vendor_type,
    u.vendor_id,
    a.agency_id AS vendor_detail_id,
    a.name AS vendor_name,
    a.licence_number AS contact_info,
    a.logo AS extra_info
FROM tbl_users u
LEFT JOIN tbl_agency a ON u.vendor_type = 'agency' AND u.vendor_id = a.agency_id
WHERE u.vendor_type = 'agency';

方案2：LEFT JOIN双表+条件判断统一字段

同时关联两个供应商表，通过CASE根据vendor_type取对应字段，结果字段统一：

SELECT 
    u.user_id,
    u.username,
    u.vendor_type,
    u.vendor_id,
    CASE 
        WHEN u.vendor_type = 'company' THEN c.company_id
        ELSE a.agency_id
    END AS vendor_detail_id,
    CASE 
        WHEN u.vendor_type = 'company' THEN c.name
        ELSE a.name
    END AS vendor_name,
    CASE 
        WHEN u.vendor_type = 'company' THEN c.contact_number
        ELSE a.licence_number
    END AS vendor_identifier,
    CASE 
        WHEN u.vendor_type = 'company' THEN c.reg_date
        ELSE a.logo
    END AS vendor_extra_info
FROM tbl_users u
LEFT JOIN tbl_company c ON u.vendor_type = 'company' AND u.vendor_id = c.company_id
LEFT JOIN tbl_agency a ON u.vendor_type = 'agency' AND u.vendor_id = a.agency_id;

方案说明 #

• 方案1适合需要严格区分不同供应商类型字段含义的场景，结果中不同类型的供应商信息字段对应各自的业务属性
• 方案2适合需要统一展示供应商信息的场景，所有供应商信息都映射到相同的字段名中

内容的提问来源于stack exchange，提问作者Lets-c-codeigniter