mySQL select query, get data from multiple tables

Question

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.

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

$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.

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.

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.

I have used GROUP BY to avoid duplicate entries with LEFT JOIN.

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.

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

Table Structure

table1

 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

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

    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.

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

if there is a logical connection between tables, duplicates should not be a problem. if there is not, then JOIN is the wrong way to go. for clarification, could you show your table structures and sample data? — Franz Gleichmann, Jan 22 '16 at 06:21
Can you give an example of the data in the base tables and the result you're expecting from those rows? — reaanb, Jan 22 '16 at 06:21
Perhaps you want SELECT DISTINCT rather than GROUP BY. This way, you will get multiple non-unique rows, if they exist. — Ken Clubok, Jan 22 '16 at 06:23
You kind of contradict yourself saying "there are multiple entries in tbl2..." and "without having multiple rows". Unless you give example to what you mean we can only guess — Gavriel, Jan 22 '16 at 06:28
I don't get what you are trying to get as a result. You want to show the info of the product list IF they were used even once in each of the 9 tables? Or you want to show the products which were present in ALL 9 tables at the same time? — El Gucs, Jan 22 '16 at 06:41
And what do you want to see if he ordered pizza every day for a week? — Gavriel, Jan 22 '16 at 06:43
Possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — Shadow, Jan 22 '16 at 06:49
I have 9 tables because it is designed like this. There are 9 different parameters that have many more sub parameters — Gags, Jan 22 '16 at 08:27
@Gusstavv Gil ... i want to show the products which were present in ALL 9 tables at the same time — Gags, Jan 22 '16 at 08:40
Hmmm... then a `left join` is not needed. A simple `inner join` would do the trick. — El Gucs, Jan 22 '16 at 08:46
@Gags You have to see my answer. i want to tell you one thing for "More Details" you have to use PHP. Click on this either new query will run or get data from prev data. prev data will be modified if you want to get data from prev query. — Monty, Jan 22 '16 at 09:06

score 1 · Answer 1 · answered Jan 22 '16 at 06:46

1

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.

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:

SELECT * FROM table3 WHERE userID = $id

answered Jan 22 '16 at 06:46

reaanb

9,806
2
23
37

but i do not want comma separated Data – Gags Jan 22 '16 at 07:39
You could query without grouping over order rows, then process the data in PHP. SQL databases expect and return at least first-normal form data, they're not report/UI generators. – reaanb Jan 22 '16 at 07:52
yup.. but how to process data in PHP... any idea?? – Gags Jan 22 '16 at 08:26
If you select from table2 without grouping, `foreach ($rows as $row) $orders[$row['userID'][] = $row['order'];` would group the data into a set of orders per user ID. – reaanb Jan 22 '16 at 09:22
Using the grouped query in my answer, `foreach ($rows as $row) $orders[$row['id']] = explode(',', $row['orders']);` would give the same result. – reaanb Jan 22 '16 at 09:24

Monty · Accepted Answer · 2016-01-22T08:58:19.890

1

Database : 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

edited Jan 22 '16 at 08:58

answered Jan 22 '16 at 08:52

Monty

1,110
7
15

this seems to be useful :) ,, but now i decided to change my approach,.. instead of order, order1 and order3 .. i will show comma separated values in orders column and then on more details i will fire another query to fetch data from rest of the tables. – Gags Jan 22 '16 at 09:10
do not know who has downvoted my question... i have explained each bit here – Gags Jan 22 '16 at 09:10
@Gags As i know there is no option to see who downvoted you. – Monty Jan 22 '16 at 09:12
@Gags Why choose this answer if you're going to use the approach I suggested? – reaanb Jan 22 '16 at 09:28
because @Monty has given the idea how to follow my earlier approach – Gags Jan 22 '16 at 09:30

score 0 · Answer 3 · answered Jan 22 '16 at 06:55

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.

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

But with this query, only data from table1 is returned – Gags Jan 22 '16 at 08:23 — Gags, Jan 22 '16 at 08:23

Ankit Chandel · Answer 4 · 2016-01-22T08:51:34.753

0

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...

edited Jan 22 '16 at 08:51

answered Jan 22 '16 at 08:40

Ankit Chandel

69
4

How does it differs from my Query?? – Gags Jan 22 '16 at 08:42

mySQL select query, get data from multiple tables

4 Answers4