1

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.

Improve this question
16
  • 1
    Can you give an example of the data in the base tables and the result you're expecting from those rows? Commented Jan 22, 2016 at 6:21
  • 1
    Perhaps you want SELECT DISTINCT rather than GROUP BY. This way, you will get multiple non-unique rows, if they exist. Commented Jan 22, 2016 at 6:23
  • 1
    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 Commented Jan 22, 2016 at 6:28
  • 3
    You forgot to add the expected result Commented Jan 22, 2016 at 6:29
  • 1
    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? Commented Jan 22, 2016 at 6:41

4 Answers 4

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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.
do not know who has downvoted my question... i have explained each bit here
@Gags As i know there is no option to see who downvoted you.
@Gags Why choose this answer if you're going to use the approach I suggested?
because @Monty has given the idea how to follow my earlier approach
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
Improve this answer

5 Comments

but i do not want comma separated Data
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.
yup.. but how to process data in PHP... any idea??
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.
Using the grouped query in my answer, foreach ($rows as $row) $orders[$row['id']] = explode(',', $row['orders']); would give the same result.
0

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.

Improve this answer

1 Comment

But with this query, only data from table1 is returned
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...

Improve this answer

1 Comment

How does it differs from my Query??

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.