无法在编写MySQL查询时从多个表中获取精确的数据集。

huangapple go评论64阅读模式
英文:

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 

huangapple
  • 本文由 发表于 2020年1月6日 17:30:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/59609599.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定