我正在try 编写一个SQL查询来列出所有用户以及供应商信息. 在tbl_USERS表中，列VENDOR_TYPE只能有COMPANY或PROJECTION，而我正在存储 根据供应商类型的值，供应商ID中的公司ID或机构ID.
Example 1:在tbl_users的第一行，user_id=1和vendor_type的值是company，所以我想从tbl_company表中获得供应商数据，其中company_id(vendor_id) = 2.
Example 2:同样，在tbl_users的第二行中，user_id=2和vendor_type的值是agency，所以我想从tbl_agency表中获得供应商数据，其中agency_id(vendor_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 |
+-----------+-------------+---------------+--------------+
我try 了下面的查询，但没有得到结果.
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`