我在mySQL中遇到了一个查询问题。
实际上,我的mySQL查询如下所示:
___Invoices表
|--------|---------------|
| IVC_Id | IVC_BookingId |
|--------|---------------|
| 1 | 99 |
|--------|---------------|___Bookings表
|--------|--------------|---------------|---------------|
| BOO_Id | BOO_ClientId | BOO_CompanyId | BOO_BillingId |
|--------|--------------|---------------|---------------|
| 99 | 44 | 46 | 0 |
|--------|--------------|---------------|---------------|__Kardex表
|--------|----------|----------|-------------|
| KDX_Id | KDX_Type | KDX_Name | KDX_Company |
|--------|----------|----------|-------------|
| 44 | client | Luka | |
| 46 | company | | Google |
| 78 | billing | Georgia | |
|--------|----------|----------|-------------|我如何连接这三张桌子?在决赛中我需要的是这样一个数组:
$contact = array (
"client" => array("KDX_Id" => 44, "KDX_Name" => "Luka"),
"company" => array("KDX_Id" => 46, "KDX_Company" => "Google"),
"billing" => array() // Noting here because no billing contact was link
);我从查询开始:
SELECT *
FROM ___Invoices
JOIN ___Bookings
ON ___Invoices.IVC_BookingId=___Bookings.BOO_Id
JOIN ___Kardex
ON ___Bookings.BOO_ClientId =___Kardex.KDX_Id
OR ___Bookings.BOO_CompanyId =___Kardex.KDX_Id
OR ___Bookings.BOO_CompanyId =___Kardex.KDX_Id
WHERE IVC_Id=1但不起作用。
能帮个忙吗?
谢谢。
发布于 2017-11-24 18:10:16
使用联合行动。只有当所有3个查询返回相同数量的结果时,这才能工作。就像这样:
SELECT KDX_Id, KDX_Type, KDX_Name FROM (
(SELECT K.KDX_Id, K.KDX_Type, K.KDX_Name, B.BOO_Id
FROM ___Kardex AS K
JOIN ___Bookings AS B ON K.KDX_Id = B.BOO_ClientId)
UNION
(SELECT K.KDX_Id, K.KDX_Type, K.KDX_Company, B.BOO_Id
FROM ___Kardex AS K
JOIN ___Bookings AS B ON K.KDX_Id = B.BOO_CompanyId)
UNION
(SELECT K.KDX_Id, K.KDX_Type, K.KDX_Name, B.BOO_Id
FROM ___Kardex AS K
JOIN ___Bookings AS B ON K.KDX_Id = B.BOO_BillingId) ) AS TEMP
JOIN ___Invoices AS I ON I.IVC_BookingId = TEMP.BOO_ID
WHERE I.IVC_Id = 1其结果将是:
ID TYPE NAME (or company)
44 client Luka
46 company Google 然后在PHP代码中,循环每一行结果,并使用类型(第二列)填充数组。
$contacts = array($results[1] => array($results[0] => $results[1]));注意:我测试了查询,而不是PHP,但是您知道了。
编辑注意:工会的结果现在被定义为临时的。然后将TEMP加入到表___Invoices中,以允许选择要筛选的发票。
https://stackoverflow.com/questions/47478042
复制相似问题