mysql - join table.A with table.B, table.B having multiple rows with respect to one id from table.A -

e.g

select id,product products;  id product 1  iphone 6  2  dell inspiron 

pid foreign key referencing product id in products

select * product_image;  id pid image 1  1   he7gu8h9d54w.jpg 2  2   jgeywfyu3r34.jpg 3  1   drtsw54e452r.jpg 4  2   weyr63tr236r.jpg 

after joining, getting this..

id product       img_id pid image 1  iphone 6      1      1   he7gu8h9d54w.jpg 1  iphone 6      3      1   drtsw54e452r.jpg 2  dell inspiron 2      2   jgeywfyu3r34.jpg 2  dell inspiron 4      1   drtsw54e452r.jpg 

i'm getting multiple rows product_image respect 1 id in product table want 1 row product_image respect product_id...plz help..

i want this...

id product       img_id pid image 1  iphone 6      1      1   he7gu8h9d54w.jpg 2  dell inspiron 4      1   drtsw54e452r.jpg 

for each product, can use not exists make sure no image lower id exists:

select p.id, p.product, pi.id, pi.pid, pi.image products p   join product_image pi on p.id = pi.pid not exists (select * product_image pi2                   pi2.pid = pi.pid                     , pi2.id < pi.id) 

alternatively, have sub-query returns each pid's minimum id, join 1 more time sub-query:

select p.id, p.product, pi.id, pi.pid, pi.image products p   join product_image pi on p.id = pi.pid   join (select pid, min(id) id product_image group pid) pi2       on pi.id = pi2.id , pi.pid = pi2.pid 

may execute faster on mysql.