PHP + mysql - left join - three tables

Question

I've got a weird question. I have three tables: - table_a with columns (id, product_id, customer_id, location, phone) - table_b with columns (id, product_id, product_name) - table_c with columns (id, customer_id, customer_name)

What I would like to display is a table with following content: table_a.id, product_name, customer_name, location, phone

So I assume somehow I should use left join table_b, table_c with table_a.

I've tried multiple approaches like:

SELECT*FROM table_a INNER JOIN table_b, table_c
ON table_a.product_id = table_b.product_id
ON table_a.customer_id = table_c.customer_id

I want to avoid making duplicates of product_id/customer_id in final table..

Tuan Duong · Accepted Answer · 2016-03-16 17:41:20Z

2

You need to join sequently table_a with table_b to get product_name, then table_a with table_c to get customer_name. You can try with this:

SELECT table_a.id, product_name, customer_name, location, phone
FROM table_a 
    INNER JOIN table_b ON table_a.product_id = table_b.product_id
    INNER JOIN table_c ON table_a.customer_id = table_c.customer_id

answered Mar 16, 2016 at 17:41

Tuan Duong

5153 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

haakym · Accepted Answer · 2016-03-16 17:44:40Z

2

It can help to specify the table name prior to the table attribute in your select statement. Hope this helps!

select
    table_a.id,
    table_b.product_name,
    table_c.customer_name,
    table_a.location,
    table_a.phone
from table_a
left join table_b
    on table_a.product_id = table_b.product_id
left join table_c
    on table_a.customer_id = table_c.customer_id

answered Mar 16, 2016 at 17:44

haakym

12.4k12 gold badges72 silver badges101 bronze badges

3 Comments

Paweł Skaba Over a year ago

do you know any specific PHP class or script which can use PDO to display large amount of data? everytime when I do that, im getting 500 error. What is more weird - my code: codepad.org/7HGBxmkq used in phpmyadmin - create a table with repeated fields.. e.g. my row with ID #1, is copied about 150 times?

haakym Over a year ago

I use Laravel for most of my projects, so mostly using eloquent for any DB interaction - I believe it uses PDO. laravel.com/docs/5.2/eloquent If you're getting a 500 error it's probably because of a timeout, try to limit your query to 10 rows to see if it works and if it does change your php settings to allow more than 30 secs for your query to execute. In any case you may want to consider pagination of results instead of spitting out a huge load of data to the screen.

haakym Over a year ago

Better to ask another question if you're having further issues on a different query

Indrasis Datta · Accepted Answer · 2016-03-17 16:09:05Z

1

You can get all the other fields, but which id are you trying to get? All 3 tables have id field.

Assuming you can take the id of first table:

SELECT table_a.id, table_b.product_name as product_name, table_c.customer_name as customer_name, table_a.location as location, table_a.phone as phone

FROM table_a INNER JOIN table_b

ON table_a.product_id = table_b.product_id

INNER JOIN table_c

ON table_a.customer_id = table_c.customer_id

Hope this helps.

Peace! xD

edited Mar 17, 2016 at 16:09

answered Mar 16, 2016 at 17:44

Indrasis Datta

8,6182 gold badges18 silver badges32 bronze badges

1 Comment

Paweł Skaba Over a year ago

It's weird. I've used your scheme - here is the query: codepad.org/Z7HY5LEn but when i'm trying this query even via phpmyadmin Im getting 1064 error - it points out that on last three lines there is an error. Why is that?

Collectives™ on Stack Overflow

PHP + mysql - left join - three tables

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related