CODEWITHSUNDEEP
mysql
lifeson
lifeson

Reputation: 171

Return single record where multiple linked records exist in MySQL

I have three tables

POI contains unique rows with an unique ID for each row

Images which may/may not contain multiple records for each poi

Reviews may also contain multiple records for each POI in table 1

I want to retrieve all rows from Reviews and if there is image in Images get the file name of the first record AND include name country from POI

This is what I have so far:

SELECT
  Reviews.review_id,
  Reviews.poi_id,
  Reviews.review,
  POI.Name,
  Images.file
FROM
  Reviews
  LEFT JOIN POI ON Reviews.poi_id = POI.ID
  LEFT JOIN Images ON Reviews.poi_id = Images.ID
ORDER BY
  POI.Name;

But this is returning multiple records (i.e. a record for each image linked to the POI) Can this be modified to just one record for each review even though there may be multiple images? Or

Upvotes: 0

Views: 174

Answers (2)

unique2
unique2

Reputation: 2302

You can do this with a correlated subquery:

SELECT
  Reviews.review_id
  Reviews.review_id,
  Reviews.poi_id,
  Reviews.review,
  POI.Name,
  SELECT (
    Images.file
    FROM Images 
    WHERE Reviews.poi_id = Images.ID
    ORDER BY Images.ID ASC
    LIMIT 1
  )
FROM
  Reviews
JOIN POI ON Reviews.poi_id = POI.ID
ORDER BY
  POI.Name;

Upvotes: 1

hcarver
hcarver

Reputation: 7214

I think you just want this:

SELECT
  DISTINCT Reviews.review_id,
  Reviews.poi_id,
  Reviews.review,
  POI.Name,
  Images.file
FROM
  Reviews
  LEFT JOIN POI ON Reviews.poi_id = POI.ID
  LEFT JOIN Images ON Reviews.poi_id = Images.ID
ORDER BY
  POI.Name;

Let me know if that doesn't do what you want.

Upvotes: 1

Related Questions