SQL Server Return 1 Row Per Boat

Question

Basically, what I want to do is join 4 tables together and return 1 row for each boat.

Table Layouts

[Boats]

id, date, section, raft

[Photos]

id, boatid, pthurl, purl

[River_Company]

id, sort, company, company_short

[River_Section]

id, section

Its very simple as far as structure, however, I've having the time of my life trying to get it to return only 1 row. No boat will ever be on the same day, the only thing that's messing this up is the photo table.

If you know a better way for it to return the record table for all the boats boats and only 1 photo from the photo table, please, please post it!!

Desired Format

boats.id, boats.date, river_company.company, river_section.section, photos.purl, photos.pthurl

Answer 1

Assuming your ID column is the relation that you have designed:

SELECT Boats.* FROM Boats
  LEFT OUTER JOIN Photos on Photos.ID = 
            (
              SELECT TOP 1 Photos.ID FROM Photos 
                 INNER JOIN Boats ON Photos.BoatID = Boats.ID
            )
  INNER JOIN River_Company on River_Company.ID = Boats.ID
  INNER JOIN River_Section on River_Section.ID = Boats.ID

So basically, this will:

• Guarantee the maximum row count of 1. (It's a bit dirty, but fact is if you have more than one photo, more than one link will be returned otherwise)

• If there are no photo's, the boat will still be returned

Answer 2

It's basically how joins work. Since boats and photos are in one-to-many relationships and you want one-to-one-like query, you need to explicitly express it with predicate. For example:

select b.*
from
  boats b
    inner join photos p
      on b.id = p.boatid
where p.id = (select max(id) from photos where boatid = b.id)