CODEWITHSUNDEEP
phpmysqljoin
Legend1989
Legend1989

Reputation: 664

mySQL JOIN on one to many

I have multiple tables that are linked together on the search page. They are currently queried like this:

SELECT * FROM tripreportentries 
    JOIN tripreport ON tripreportentries.reportid = tripreport.tripreportid 
    JOIN user_details on tripreportentries.userid = user_details.userid
WHERE TripEntryHeading LIKE '%%' AND tripreportentries.ispublic = 1
ORDER BY tripreportentryid

I have since created a new table called tripphotos with the table columns named:

The issue is each entry can have more than one corresponding photo in the photo table.

I am looking to find a way to JOIN the new table to the statement above but not return several rows for each entry id.

Any ideas on how to join this table?

EDIT - I also want to just tie one image to the returned row and not all of them just so I can show the image as a thumbnail on the browse page

Upvotes: 1

Views: 60

Answers (1)

xQbert
xQbert

Reputation: 35323

I normally would list every column and group by the non aggregates, but mySQL will allow this I believe; given the mySQL group by extensions; and we expect the tripReport and user_Details columns to be the same for each joined photoname.

SELECT tripReport.*, user_details.*, max(photoname) as maxPhotoPerReport 
    FROM tripreportentries 
    JOIN tripreport ON tripreportentries.reportid = tripreport.tripreportid 
    JOIN user_details ON tripreportentries.userid = user_details.userid
    JOIN tripphotos ON tripreportentries.reportid = tripPhotos.ReportID
WHERE TripEntryHeading LIKE '%%' AND tripreportentries.ispublic = 1
GROUP BY ReportId
ORDER BY tripreportentryid

or we could do this using an inline view to get the max photo for each report and then join that set to your base query.

SELECT * 
    FROM tripreportentries 
    JOIN tripreport ON tripreportentries.reportid = tripreport.tripreportid 
    JOIN user_details on tripreportentries.userid = user_details.userid
    JOIN (SELECT max(photoName) as MaxPhotoPerReport, ReportID 
          FROM TripPhotos 
          GROUP BY  ReportID) B 
        ON B.ReportID = tripReport.ReportId
WHERE TripEntryHeading LIKE '%%' AND tripreportentries.ispublic = 1
ORDER BY tripreportentryid

Upvotes: 1

Related Questions