1 
SELECT Boats.id, Boats.date, Boats.section, Boats.raft, river_company.company, river_section.section AS river FROM Boats INNER JOIN river_company ON Boats.raft = river_company.id INNER JOIN river_section ON Boats.section = river_section.id ORDER BY Boats.date DESC, river, river_company.company

Returns everything I need. But how would I add a [Photos] table and count how many times Boats.id occurs in it and add that to the returned rows.

So if there are 5 photos for boat #17 I want the record for boat #17 to say PhotoCount = 5

Improve this question

7 Answers 7

Reset to default
2

You could add a LEFT JOIN to a sub query as follows:

LEFT JOIN (SELECT COUNT(p.id) num, p.boatID FROM photos p GROUP BY p.boatID) sub_photos ON (sub_photos.boatID = Boats.id)

And then reference sub_photos.num in your SELECT fields.

It would look something like this:

SELECT Boats.id, Boats.date, Boats.section, Boats.raft, river_company.company, river_section.section AS river, sub_photos.num AS number_of_photos FROM Boats INNER JOIN river_company ON Boats.raft = river_company.id INNER JOIN river_section ON Boats.section = river_section.id LEFT JOIN (SELECT COUNT(p.id) num, p.boatID FROM photos p GROUP BY p.boatID) sub_photos ON (sub_photos.boatID = Boats.id) ORDER BY Boats.date DESC, river, river_company.company
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Daniel, aside from a minor col name correction, its prefect! Thank you! Boats.boatID = Boats.ID
@Tyler: Just noticed, and fixed... I'm glad this helped :)
2

I presume boatsId is a column in the Photos table? The easiest way would be with a subselect:

SELECT ...whatever columns..., (SELECT COUNT(*) FROM Photos WHERE boatsId = Boats.id) FROM ...rest of your query
Improve this answer

2 Comments

Subquery returned more than 1 value.
If you used this method you would have filter the subselect on boat ID. I think joining on a subquery is better for this kind of thing.
1 
SELECT Boats.id, Boats.date, Boats.section, Boats.raft, river_company.company, river_section.section AS river (SELECT count(*) FROM Photos WHERE boatsID = Boats.id) FROM Boats INNER JOIN river_company ON Boats.raft = river_company.id INNER JOIN river_section ON Boats.section = river_section.id ORDER BY Boats.date DESC, river, river_company.company

Give this one a try.

Improve this answer

1 Comment

Minor changes and it works great! Thank you. SELECT Boats.id, Boats.date, Boats.section, Boats.raft, river_company.company, river_section.section AS river, (SELECT count(*) FROM Photos WHERE boatID = Boats.id) as PicCount FROM Boats INNER JOIN river_company ON Boats.raft = river_company.id INNER JOIN river_section ON Boats.section = river_section.id ORDER BY Boats.date DESC, river, river_company.company
1

Another way:

SELECT Boats.id, Boats.date, Boats.section, Boats.raft, river_company.company, river_section.section AS river Count(Photos.ID) AS PhotoCount FROM Boats INNER JOIN river_company ON Boats.raft = river_company.id INNER JOIN river_section ON Boats.section = river_section.id OUTER JOIN Photos ON Boats.id = Photos.BoatID ORDER BY Boats.date DESC, river, river_company.company GROUP BY Boats.id, Boats.date, Boats.section, Boats.raft, river_company.company, river_section.section
Improve this answer

Comments

1

You haven't provide any information regarding the structure of the Photos table but something like this should work for you.

SELECT Boats.id, Boats.date, Boats.section, Boats.raft, river_company.company, river_section.section AS river, PhotoCount.NumberOfBoats as PhotoCount FROM Boats INNER JOIN river_company ON Boats.raft = river_company.id INNER JOIN river_section ON Boats.section = river_section.id Left Join ( Select b2.Id, Count(b2.Id) as NumberOfBoats From Boats b2 Join Photos p on b2.Id = p.BoatId Group By b2.Id )BoatCount on BoatCount.Id = Boats.Id ORDER BY Boats.date DESC, river, river_company.company
Improve this answer

1 Comment

I'll try to post more details in the future. Thanks again.
1

Another way

SELECT DISTINCT Boats.id, Boats.date, Boats.section, Boats.raft, river_company.company, river_section.section AS river, count(photos.boatid) OVER(PARTITION BY photos.boatid) FROM Boats INNER JOIN river_company ON Boats.raft = river_company.id INNER JOIN river_section ON Boats.section = river_section.id LEFT JOIN photos on photos.boatid = Boats.id ORDER BY Boats.date DESC, river, river_company.company
Improve this answer

Comments

1

Select YourID, COUNT(*) FROM YourTable GROUP BY YourID

This works just fine for when I'm trying to track down where record count might be different based on an ID or any other column.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.