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

Question

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

That is because you have two entries in table B, you can use "GROUP BY B.pid" in query to adjust.But i dont think that is the right way. — Sanal K, Feb 08 '16 at 06:48
Can you define which of the multiple images you want for every product? — Smutje, Feb 08 '16 at 07:07

score 2 · Accepted Answer · edited Feb 10 '16 at 08:15

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.

score 0 · Answer 2 · answered Feb 08 '16 at 07:25

0

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)

answered Feb 08 '16 at 07:25

ScaisEdge

131,976
10
91
107

score 0 · Answer 3 · edited May 23 '17 at 12:31

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 |

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

3 Answers3