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