mySQL选择查询，从多个表中获取数据

I am trying to fetch data from mySql DB. Total there are 9 tables and i have to display the product list accordingly from all tables.

我正在尝试从mySql DB中获取数据。总共有9个表，我必须从所有表中显示相应的产品列表。

I thought of using JOINs and tried LEFT JOIN as below:

我想到了使用JOIN，并尝试使用LEFT JOIN，如下所示:

$query="SELECT table1.*,tbl2.*,tbl3.*,tbl4.*,tbl5.*,tbl6.*,tbl7.*",tbl8.*,tbl9.* FROM 
table1 
LEFT JOIN tbl2 ON table1.pid=tbl2.pid 
LEFT JOIN tbl3 ON table1.pid=tbl3.pid 
LEFT JOIN tbl3 ON table1.pid=tbl4.pid ... and so on upto tbl9 GROUP BY table1.pid";

Here Table1 is the main table and pid is FK to all tables from tbl2 to tbl9.

表1是主表，pid为从tbl2到tbl9的所有表的FK。

Note: Here i have used .* on all tables to avoid long query but in actual DB operation only particular columns are mentioned to improve performance.

注意:这里我已经在所有表上使用了.*，以避免长查询，但是在实际的DB操作中，只提到特定的列来提高性能。

Now actual problem is that i am not getting all records from tables using LEFT JOIN. Only last rows are retrieved of each entry corresponding in table1.

现在实际的问题是，我没有使用左连接从表中获取所有记录。仅检索表1中对应的每个条目的最后一行。

I have used GROUP BY to avoid duplicate entries with LEFT JOIN.
我使用了GROUP BY来避免带有左连接的重复条目。

Example of Data.

数据的例子。

Suppose table1 has one product with id 2 then there are multiple entries in tbl2,tbl3 and so on.. with reference to id 2.

假设表1有一个带有id 2的产品，那么在tbl2、tbl3等中有多个条目。关于id 2。

How can i get all data from other tables too without having duplicate rows.

如何从其他表中获取所有数据，而不需要重复行。

Table Structure

表结构

table1

表1

 id     |     name   |     lastName  
 ---------------------------------------
 1      |    john    |      doe
 2      |    helen   |      keller

table2

表二

 The userID column is a foreign key that references John Doe, so John orders 3 items.

  id     |   userID   |     order 
  ---------------------------------------
  1      |    1       |      pizza
  2      |    1       |      pasta
  3      |    1       |      lasagna

Table3

Table3

The userID column is a foreign key that references John Doe, so John leaves 5 reviews.

userID列是引用John Doe的外键，所以John留下5个评论。

    id     |   userID  |     rating   |  comment
    -------------------------------------------------
    1      |    1       |      5/5     |  was good
    2      |    1       |      5/5     |  excellent
    3      |    1       |      4/5     |  great
    4      |    1       |      4/5     |  great
    5      |    1       |      4/5     |  great

Table Structure is copied from HERE because it is same as mine.

表结构是从这里复制的，因为它和我的一样。

Result shall be as below:

结果如下:

id name lastname order order1 order2  MoreDetails
-------------------------------------------------
1  John  doe     pizza pasta  lasgana  click to view

Now when person click on view then a popup is displayed with all data from table 3.

现在，当用户单击view时，会显示一个弹出窗口，显示来自表3的所有数据。

Pivot table is no needed here because Data representation is different.

这里不需要Pivot表，因为数据表示不同。

4 个解决方案

#1

Database : Postgresql

数据库:Postgresql

Try this:

试试这个:

SELECT t1.id, t1.name, t1.lastname, (array_agg(t2.order))[1] as order, 
(array_agg(t2.order))[2] as order1, (array_agg(t2.order))[3] as order2, 
'Clicks to view' as "Moredetails"
FROM table1 as t1 
LEFT JOIN table2 as t2 ON t1.id = t2."userID" 
LEFT JOIN table3 as t3 ON t1.id = t3."userID" 
WHERE t1.id = 1 
GROUP BY 1,2,3

OUTPUT :

输出:

id name lastname order order1 order2  MoreDetails
-------------------------------------------------
1  John  doe     pizza pasta  lasgana  click to view

#2

You're asking for a pivot table with possibly unlimited columns, and some kind of user interface/interaction that loads related data. That's not how SQL databases work.

您需要一个具有可能无限列的pivot表，以及某种加载相关数据的用户界面/交互。这不是SQL数据库的工作方式。

The closest I can come to what you're asking is:

最接近你的问题的是:

SELECT table1.*, GROUP_CONCAT(table2.order) AS orders, 'click to view' AS MoreDetails
FROM table1
LEFT JOIN table2 ON table1.id = table2.userID
GROUP BY table1.id

This will combine the orders into a comma-separated list in one column called orders, and 'click to view' is just a string. User interaction can be handled in PHP, you could receive the id the user clicked on then run a new query to retrieve the related info:

这将把订单合并到一个名为orders的列中的逗号分隔列表中，而‘click to view’只是一个字符串。用户交互可以用PHP进行处理，您可以接收用户点击的id，然后运行新的查询来检索相关信息:

SELECT * FROM table3 WHERE userID = $id

#3

Not completely sure what results you want to obtain. Considering you want to obtain the product info of those entries which were on each table, at least once, then you need to make a union of those occurrences, group them (to avoid duplicates) in a subquery and show the details of each of those found products.

不完全确定你想要得到什么结果。考虑到您希望获取每个表上的条目的产品信息(至少一次)，那么您需要对这些事件进行联合，在子查询中对它们进行分组(以避免重复)，并显示每个发现的产品的详细信息。

SELECT table1.* FROM table1 inner join (
SELECT pid FROM tbl2 WHERE your_filter = ?
UNION
SELECT pid FROM tbl3 WHERE your_filter = ?
UNION
SELECT pid FROM tbl4 WHERE your_filter = ?
UNION
...
GROUP BY pid) as subqueryIds
on table1.pid = subqueryIds.pid

If you don't need any kind of filtering on each table you can completely supress the WHEREs.

如果您不需要对每个表进行任何过滤，您可以完全压制where。

Not sure if this is what you are looking for. Hope it helps somehow.

不确定这是不是你要找的。希望它能帮助。

#4

Try joining tables according to relations like

尝试根据类似的关系连接表

SELECT table1.*,tbl2.*,tbl3.*,tbl4.*,tbl5.*,tbl6.*,tbl7.*,tbl8.*,tbl9.* FROM 
table1 
LEFT JOIN tbl2 ON table1.pid=tbl2.pid 
LEFT JOIN tbl3 ON table2.pid=tbl3.pid

and so on...

等等……

#1