CODEWITHSUNDEEP
phpmysqlsql
John K
John K

Reputation: 353

sql query to join two tables

I have 2 tables(mysql), one for product name and other for product images.

tblproducts --> id(int,pk), name(varchar)

tblphotos ---> id(int,pk), productid(int,fk), photo, order

in tblphotos the order field is the display order of the photos (1,2,3...)

There can be more than 1 photo for each product. I need the first photo of the product(order =1)

I need is tblproducts.id,tblproducts.name,tblphotos.photo(photo must be the one with order 1)

I wrote the following query,but I am product name is repeating 

   SELECT tblproducts.id,tblproducts.name, tblphotos.photo 
     FROM tblproducts 
LEFT JOIN tblphotos on tblphotos.productid = tblproducts.id

one more thing :: I need to sort the order in ascending order

Upvotes: 0

Views: 2263

Answers (3)

Abdul Manaf
Abdul Manaf

Reputation: 4888

i think this will work for you

SELECT tblproducts.id, tblproducts.name, tblphotos.photo 
FROM tblproducts 
LEFT JOIN tblphotos on tblphotos.productid = tblproducts.id
   and tblphotos.order=1;

Upvotes: -1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

try this:

SELECT tblproducts.id,tblproducts.name, tblphotos.photo 
FROM tblproducts LEFT JOIN tblphotos on tblphotos.productid = tblproducts.id 
and tblphotos.order=1

Upvotes: 0

Tom H
Tom H

Reputation: 47402

If you only want the first photo (the one with order = 1) then you need to specify that in your query:

SELECT
    PR.id AS product_id,
    PR.name,
    PH.photo
FROM
    Products PR
LEFT OUTER JOIN Photos PH ON
    PH.product_id = PR.id AND
    PH.`order` = 1

Since order is likely a reserved word, I've enclosed it in the default quoted identifier for MySQL. You'd be better off with a different column name though. Also, I can't bring myself to write a query with "tbl" in front of table names.

Upvotes: 3

Related Questions