MySQL join two table query

Question

I have two table, product and productimages

product table;

|----------------Table:product-|
|ID | tr_productname           |
|---|--------------------------|
| 1 | Computer                 |
| 2 | Telephone                |
| 3 | Television               |
| 4 | Mouse                    |
|------------------------------|

productimages table;

|-----------Table:productimages-|
|ID | productid  |  imageurl    |
|---|------------|--------------|
| 1 |     1      | computer.jpg |
| 2 |     1      | computer2.jpg|
| 3 |     2      | telephone.jpg|
| 4 |     2      | x.jpg        |
| 5 |     2      | abc.jpg      |
| 6 |     3      | tv.jpg       |
|-------------------------------|

I want list products with image but if image does'nt seem to record. I want list all product.

MyQuery;

SELECT product.id, productcode, tr_productname, brand, imageurl 
FROM product 
LEFT JOIN productimages 
ON product.id = productimages.productid 
GROUP BY productimages.productid 
ORDER BY product.id DESC

Result;

|-----------------------------------RESULT----|
|ID | tr_productname           |    imageurl  |
|---|--------------------------|--------------|
| 1 | Computer                 | computer.jpg |
| 2 | Telephone                | telephone.jpg|
| 3 | Television               | tv.jpg       |
|------------------------------|--------------|

But no product with "4" ID

I guess you are looking for all images for per product and if yes try group_concat(imageurl) as imageurl — Abhik Chakraborty
– Abhik Chakraborty, Commented Jun 25, 2014 at 11:32
I try join and in select but didn't work. It work but 4th record not seem. Need productimages record for seem a product. I want get list all product. Didn't work group_concat(imageurl) as imageurl — wbdvlpr
– wbdvlpr, Commented Jun 25, 2014 at 11:37

Sadikhasan · Accepted Answer · 2014-06-25 11:32:17Z

1

Try to change GROUP BY productimages.productid to product.id like this

SELECT product.id, productcode, tr_productname, brand, imageurl 
FROM product 
LEFT JOIN productimages 
ON product.id = productimages.productid 
GROUP BY product.id 
ORDER BY product.id DESC

answered Jun 25, 2014 at 11:32

Sadikhasan

18.6k23 gold badges85 silver badges126 bronze badges

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

Comments

Charvee Shah · Accepted Answer · 2014-06-25 11:32:07Z

1

Try this ..

SELECT product.id, productcode, tr_productname, brand, imageurl 
FROM product 
LEFT JOIN productimages 
ON product.id = productimages.productid 
GROUP BY product.id 
ORDER BY product.id DESC

answered Jun 25, 2014 at 11:32

Charvee Shah

7202 gold badges6 silver badges21 bronze badges

Comments

R D · Accepted Answer · 2014-06-25 11:35:44Z

0

When you are using group by, the value involves in group by only shown. In your case you have grouped based on productimages table, in this table 4 value is not present. So use product.id in group by.

SELECT product.id, productcode, tr_productname, brand, imageurl 
FROM product 
LEFT JOIN productimages 
ON product.id = productimages.productid 
GROUP BY product.id 
ORDER BY product.id DESC

answered Jun 25, 2014 at 11:35

R D

1,33213 silver badges31 bronze badges

Comments

user3775326 · Accepted Answer · 2014-06-25 13:06:56Z

0

SELECT product.id, productcode, tr_productname, brand, imageurl 
FROM product 
LEFT JOIN productimages 
ON product.id = productimages.productid 
GROUP BY product.id 
ORDER BY product.id DESC

answered Jun 25, 2014 at 13:06

user3775326

1

1 Comment

Patrick Q Over a year ago

A code-only or query-only answer is not usually a "quality" answer. Please provide some explanation as to why you believe that this answers the question.

Collectives™ on Stack Overflow

MySQL join two table query

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related