英文:
Couldn't get the exact set of data from multiple tables when i write a mysql query
问题
我编写了一个MySQL查询，列出了一些在我的表中的网点，对于一些网点，我已经分配了一些员工，我需要列出这些网点的详细信息以及分配的员工姓名和其他详细信息。我在下面添加了我的查询：
SELECT a.outlet_id
, a.outlet_name
, a.owner_name
, a.address
, a.pin
, a.outlet_place
, a.contact_no
, a.img
, a.status
, b.name
, b.phone
, b.id
, (SELECT SUM(total_amount) FROM tbl_orders WHERE status = "delivered" AND outlet_id = a.outlet_id AND a.status = "active") totalamount
FROM tbl_outlet a
, tbl_staff b
, tbl_staff_to_outlet c
WHERE b.id = c.staff_id
AND a.outlet_id = c.outlet_id
AND b.status = "active"
GROUP
BY a.outlet_id
问题是我只得到了已分配给员工的网点和详细信息的列表。它没有列出未分配给任何员工的网点。
我有一个名为“tbl_outlet”的包含网点详细信息的表，另一个名为“tbl_staff”的包含员工详细信息的表，还有一个名为“tbl_staff_to_outlet”的表，其中包含将网点ID与员工ID关联起来的信息，以指示哪个网点分配给哪个员工。
英文:
I wrote a MySQL query to list some outlets which are in my tables and for some outlets, I have assigned some staffs and I need to list the outlets with its details and the assigned staff name and other details. I add my query below
SELECT a.outlet_id
, a.outlet_name
, a.owner_name
, a.address
, a.pin
, a.outlet_place
, a.contact_no
, a.img
, a.status
, b.name
, b.phone
, b.id
, (SELECT SUM(total_amount) FROM tbl_orders WHERE status = "delivered" AND outlet_id = a.outlet_id AND a.status = "active") totalamount
FROM tbl_outlet a
, tbl_staff b
, tbl_staff_to_outlet c
WHERE b.id = c.staff_id
AND a.outlet_id = c.outlet_id
AND b.status = "active"
GROUP
BY a.outlet_id
the problem is I got the list of outlet and details which are assigned to staff only. it didn't list out the outlets which are not assigned to any staff.
I have one table with outlet details named "tbl_outlet" and another table named "tbl_staff" which have staffs details and another table "tbl_staff_to_outlet" which have the outlet id and staff id to map which outlet is assigned to which staff.
答案1
得分: 0
我建议您使用 ANSI JOIN 语法来编写连接操作。这可以提供更清晰的代码。
您需要使用 OUTER JOIN 来返回值，即使在其他表中没有匹配的值。
SELECT a.outlet_id,
a.outlet_name,
a.owner_name,
a.address,
a.pin,
a.outlet_place,
a.contact_no,
a.img,
a.status,
b.NAME,
b.phone,
b.id,
(SELECT Sum(total_amount)
FROM tbl_orders
WHERE status = "delivered"
AND outlet_id = a.outlet_id
AND a.status = "active") AS totalamount
FROM tbl_outlet AS a
LEFT OUTER JOIN tbl_staff_to_outlet AS c ON a.outlet_id = c.outlet_id
LEFT OUTER JOIN tbl_staff AS b ON b.id = c.staff_id AND b.status = "active"
GROUP BY a.outlet_id
英文:
I would suggest you to use ANSI JOIN syntax for writing JOINS. It provides more clarity.
You have to use OUTER JOINS to return values even if there are no matching values in other tables.
SELECT a.outlet_id,
a.outlet_name,
a.owner_name,
a.address,
a.pin,
a.outlet_place,
a.contact_no,
a.img,
a.status,
b.NAME,
b.phone,
b.id,
(SELECT Sum(total_amount)
FROM tbl_orders
WHERE status = "delivered"
AND outlet_id = a.outlet_id
AND a.status = "active") AS totalamount
FROM tbl_outlet AS a
LEFT OUTER JOIN tbl_staff_to_outlet AS c ON a.outlet_id = c.outlet_id
LEFT OUTER JOIN tbl_staff AS b ON b.id = c.staff_id AND b.status = "active"
GROUP BY a.outlet_id
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论