Home Ask Login Register

Developers Planet

Your answer is one click away!

Siddharth Murari February 2016

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

e.g

select id,product from products;

id product
1  iPhone 6 
2  Dell Inspiron

pid is foreign key referencing to product id in products

select * from product_image;

id pid image
1  1   he7gu8h9d54w.jpg
2  2   jgeywfyu3r34.jpg
3  1   drtsw54e452r.jpg
4  2   weyr63tr236r.jpg

after joining, i am 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 from product_image with respect to one id in product table I want to get one row from product_image with respect to a 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

Answers


scaisEdge February 2016

You can subselect the min(id) form product_image for restrict the selection

select a.id, a.product, b.id, b.pid, b.image
from products as a, product_image as b
where a.id = b.pid
and b.id in (select min(id) from product_image group by pid)


jarlh February 2016

For each product, you can use NOT EXISTS to make sure no image with lower id exists:

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

Alternatively, have a sub-query that returns each pid's minimum id, join one more time with that sub-query:

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

May execute faster on MySQL.


murison February 2016

What you are looking is an equivalent of SELECT DISTiNCT ON but there is none in MySQL. please see this topic: Converting SELECT DISTINCT ON queries from Postgresql to MySQL

If you don't care which row will be returned, this will work:

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE prod
    (`id` int, `product` varchar(12))
;

INSERT INTO prod
    (`id`, `product`)
VALUES
    (1, 'iPhone6'),
    (2, 'DellInspiron')
;



CREATE TABLE img
    (`id` int, `pid` int, `image` varchar(16))
;

INSERT INTO img
    (`id`, `pid`, `image`)
VALUES
    (1, 1, 'he7gu8h9d54w.jpg'),
    (2, 2, 'jgeywfyu3r34.jpg'),
    (3, 1, 'drtsw54e452r.jpg'),
    (4, 2, 'weyr63tr236r.jpg')
;

Query 1:

select 
  id, pid, image 
from img
group by pid

Results:

| id | pid |            image |
|----|-----|------------------|
|  1 |   1 | he7gu8h9d54w.jpg |
|  2 |   2 | jgeywfyu3r34.jpg |

Query 2:

select 
  prod.id as prod_id, prod.product as prod_product
  , img.id as img_id, img.pid as img_pid, img.image as img_image 
from prod
join img on img.pid = prod.id
group by img.pid

Results:

| id |      product | id | pid |            image |
|----|--------------|----|-----|------------------|
|  1 |      iPhone6 |  1 |   1 | he7gu8h9d54w.jpg |
|  2 | DellInspiron |  2 |   2 | jgeywfyu3r34.jpg |

Post Status

Asked in February 2016
Viewed 3,053 times
Voted 5
Answered 3 times

Search




Leave an answer


Quote of the day: live life