CODEWITHSUNDEEP
mysql
user3733831
user3733831

Reputation: 2936

Make a SELECT query from two tables with one record from matching rows in mysql

I do have two mysql tables named Services and Images. Each service may have multiple images.

So my question is, just I need to make a SELECT query to get all services with one image to each service.

This is how I tried it. But it gave me all services and all images to each service. 

SELECT s.id
        , s.name
        , s.description
        , i.id as image_id
        , i.image
FROM  services s
LEFT JOIN images i ON i.service_id = s.id;

Can anybody tell me how I make this query correctly?

Thank you.

Upvotes: 0

Views: 28

Answers (2)

axxis
axxis

Reputation: 1004

This is the way to get all services that have only one image:

SELECT s.id
        , s.name
        , s.description
        , i.id as image_id
        , i.image
FROM  services s
JOIN images i on i.service_id = s.id
WHERE i.service_id in (
  SELECT service_id FROM images GROUP BY service_id HAVING COUNT(*) = 1
)

Upvotes: 0

Imran
Imran

Reputation: 3072

IF you need just one image for one service you can group by your service id

SELECT s.id
        , s.name
        , s.description
        , i.id as image_id
        , i.image
FROM  services s
LEFT JOIN images i ON i.service_id = s.id
GROUP BY s.id;

Upvotes: 1

Related Questions