Retrieving Data from Multiple Tables base on id

Question

With php I am trying to get data from 2 different tables :

Table : products

+-------+---------+----------+
| pcode | product | category |
+-------+---------+----------+
| 1     | jeans   | men      |
+-------+---------+----------+
| 2     | shirt   | men      |
+-------+---------+----------+


Table : colors
+-------+---------+
| pcode | colors  | 
+-------+---------+
| 1     | blue    |
+-------+---------+
| 1     | black   |
+-------+---------+
| 1     | white   |
+-------+---------+
| 2     | yellow  |
+-------+---------+

Result I want should be like :

+-------+---------+----------+---------------------+
| pcode | product | category | colors              |
+-------+---------+----------+---------------------+
| 1     | jeans   | men      | black, blue, white  |
+-------+---------+----------+---------------------+
| 2     | shirt   | men      | yellow              |
+-------+---------+----------+---------------------+

Please guide me.

Thanks in advance.

Vikram

I tried inner join & left join but result am getting is 3 rows for pcode 1 — oxEgen, Dec 14 '13 at 13:19
I recommend you edit the original question to add this info, and how the rows looked like (when it didn't work). Unfortunatelly I cannot help with the question, but it'd make the people who sees the question and can help more eager to do so. — Francisco Presencia, Dec 14 '13 at 13:23
This has been discussed here: http://stackoverflow.com/questions/7448734/sql-comma-separated-row-with-group-by-clause — mxm, Dec 14 '13 at 13:27
Franciso - I got pissed of it for long hours - so finally posted here - will take care of it next time - thanks anyways. — oxEgen, Dec 14 '13 at 13:43

score 2 · Answer 1 · answered Dec 14 '13 at 13:25

2

SELECT p.pcode,product,category,GROUP_CONCAT(colors) as colors
FROM products p JOIN colors c 
ON p.pcode=c.pcode 
GROUP BY product

answered Dec 14 '13 at 13:25

Mihai

26,325
7
66
81

Balaji Perumal · Accepted Answer · 2013-12-14T13:59:01.830

0

Mihai's Query also working..

Can also try this..

select p.*, group_concat(colors separator ',') colors from products p left outer join colors c on p.pcode=c.pcode group by p.pcode;

edited Dec 14 '13 at 13:59

answered Dec 14 '13 at 13:34

Balaji Perumal

830
1
6
20

wow! It works fine - but one issue now I got is - suppose pcode 3 items does not have any color options - it wont show data for pcode 3 :( – oxEgen Dec 14 '13 at 13:48
SELECT p.pcode,product,category,GROUP_CONCAT(colors) as colors FROM products p left outer JOIN colors c ON p.pcode=c.pcode GROUP BY product; Try this... – Balaji Perumal Dec 14 '13 at 13:58
Yup - that works fine - wow!! superb - Thanks a tonne Balaji. – oxEgen Dec 14 '13 at 14:02
Glad that you got the result.. :) – Balaji Perumal Dec 14 '13 at 14:05
Yup - got a perfect result. Thanks again. – oxEgen Dec 14 '13 at 14:06
Hi again - for getting data from 3 tables I tried : "SELECT products.pcode, products.product, GROUP_CONCAT(c.colors) as c.colors, GROUP_CONCAT(x.pimages) as x.pimages FROM products p JOIN colors c ON c.pcode=p.pcode pimages x ON x.pcode=p.pcode GROUP BY product;" But did not work :(. – oxEgen Dec 18 '13 at 09:10
SELECT products.pcode, products.product, GROUP_CONCAT(c.colors) as c.colors, GROUP_CONCAT(x.pimages) as x.pimages FROM products p LEFT OUTER JOIN colors c ON c.pcode=p.pcode LEFT OUTER JOIN pimages x ON x.pcode=p.pcode GROUP BY product; try this – Balaji Perumal Dec 18 '13 at 09:15
Thanks for your quick reply Balaji - but it did not worked :( – oxEgen Dec 18 '13 at 09:43
SELECT products.pcode, products.product, GROUP_CONCAT(c.colors) as colors, GROUP_CONCAT(x.pimages) as pimages FROM products p LEFT OUTER JOIN colors c ON c.pcode=p.pcode LEFT OUTER JOIN pimages x ON x.pcode=p.pcode GROUP BY p.product; Try this – Balaji Perumal Dec 18 '13 at 09:58
SELECT products.pcode, products.product, GROUP_CONCAT(c.colors) as colors, GROUP_CONCAT(x.pimages) as pimages FROM products p LEFT OUTER JOIN colors c ON c.pcode=p.pcode LEFT OUTER JOIN pimages x ON x.pcode=p.pcode GROUP BY p.product; Try this – Balaji Perumal Dec 18 '13 at 10:02

score 0 · Answer 3 · answered Dec 14 '13 at 13:45

NOTE:You could try to use PDO or MySqli libary(links bellow) to run this SQL Statement

You could try this SQL statement i think it will do the job but will show the rows in a different way that you requested just give it a try:

SELECT colors.pcode , products.products ,products.category ,  colors.colors
FROM colors
INNER JOIN FIRST ON colors.pcode = products.pcode

Results:
The result

Or if you wanted like you asked you should seperate the work in multiple SQL Satatement:

SELECT colors FROM colors WHERE pcode = 1

And with some SQL Sattement get all the colors
Then get the products and the productsand categorys this way:

SELECT product , category FROM products WHERE pcode = 1

And also with some like this you will get all what you need

But i would prefer the first one as it is more handy and faster.

Links:

PDO:

http://php.net/manual/en/book.pdo.php

MYSQLI:

http://www.php.net/mysqli

Have a nice day :)

Retrieving Data from Multiple Tables base on id

3 Answers3