MySQL join multiple tables?

Question

I have 3 tables

tblMarkers tblReviews tblImages

I want to return ALL the records from tblMarkers and a count of all the reviews and images for each marker. To complicate it further it should return all the markers within a radius of a known point.

so the expected result would be

MarkerID-MarkerName-ReviewCount-ImageCount

1223-SomeName-0-1

This is what I have so far and this returns a count of reviews, but I cant get the right SQL to add the count of images

SELECT
`tblMarkers`.`ID`,
`tblMarkers`.`Type`,
`tblMarkers`.`Name`,
`tblMarkers`.`Latitude`,
`tblMarkers`.`Longitude`, 
(3959 * acos( cos( radians('45.1') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('6') ) + sin( radians('45.1') ) * sin( radians( Latitude ) ) ) )  
AS distance,
Count(`tblReviews`.`marker_id`) As reviewCount
FROM
`tblMarkers`
LEFT JOIN `tblReviews` ON `tblMarkers`.`ID` = `tblReviews`.`marker_id`
GROUP BY
`tblMarkers`.`ID`,
`tblMarkers`.`Type`,
`tblMarkers`.`Name`,
`tblMarkers`.`Latitude`,
`tblMarkers`.`Longitude`
HAVING
`distance` < '50'
ORDER BY
distance;

The Images table structure is

ID [primaryKey] (same as tblMarkers.ID)
file
title

How do I add a count of all the images?

as you mentioned about the image table structure, where is the relational field with the marker table?? without it you can not get the count — skparwal
– skparwal, Commented Jun 13, 2013 at 10:56

KaeL · Accepted Answer · 2013-06-14 03:03:48Z

1

Try this:

SELECT M.ID
    , M.Type
    , M.Name
    , M.Latitude
    , M.Longitude
    , (3959 * acos(cos(radians('45.1')) * cos(radians(M.Latitude )) * cos(radians(M.Longitude) - radians('6')) + sin(radians('45.1')) * sin(radians(M.Latitude ))))  AS distance
    , IFNULL(COUNT(DISTINCT R.review_id) , 0) AS ReviewCount
    , IFNULL(COUNT(DISTINCT I.ID), 0) AS ImageCount
    FROM tblMarkers AS M
    LEFT JOIN tblReviews AS R ON R.marker_id = M.ID
    LEFT JOIN tblImages AS I ON I.marker_id = M.ID
    GROUP BY M.ID, M.Type, M.Name, M.Latitude, M.Longitude
    HAVING distance < 50
    ORDER BY distance

I bet you already know LEFT JOIN. So I used COUNT(DISTINCT R.review_id)) to count all distinct review id's (just make sure that review_id is unique). In case there are no corresponding review and image record for a specific marker record, I used IFNULL(XXX , 0) to display 0.

I used ALIASES to make your query clean and neat. Feel free to ask.

edited Jun 14, 2013 at 3:03

answered Jun 13, 2013 at 11:33

KaeL

3,6612 gold badges31 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

lifeson Over a year ago

That compiled (using MYSQL front) but when I try to run it comes up with an error "Unknown column 'R.ID' in field list" link[/link]

KaeL Over a year ago

I see, it is because there's no ID column in the tblReview table. Edited my answer.

lifeson Over a year ago

Sorted now thanks - I had to remove the DISTINCT statement from the images list to get the number of images for each marker, but otherwise it works great!

skparwal · Accepted Answer · 2013-06-13 10:58:48Z

0

IF ID field of image table is the reference of marker table ID field then the query will be:

SELECT
`tblMarkers`.`ID`,
`tblMarkers`.`Type`,
`tblMarkers`.`Name`,
`tblMarkers`.`Latitude`,
`tblMarkers`.`Longitude`, 
(3959 * acos( cos( radians('45.1') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('6') ) + sin( radians('45.1') ) * sin( radians( Latitude ) ) ) )  
AS distance,
Count(`tblReviews`.`marker_id`) As reviewCount,
Count(`tblImage`.`ID`) As imageCount,
FROM
`tblMarkers`
LEFT JOIN `tblReviews` ON `tblMarkers`.`ID` = `tblReviews`.`marker_id`
LEFT JOIN `tblImage` ON `tblMarkers`.`ID` = `tblImages`.`ID`
GROUP BY
`tblMarkers`.`ID`,
`tblMarkers`.`Type`,
`tblMarkers`.`Name`,
`tblMarkers`.`Latitude`,
`tblMarkers`.`Longitude`
HAVING
`distance` < '50'
ORDER BY
distance;

answered Jun 13, 2013 at 10:58

skparwal

1,0746 silver badges15 bronze badges

4 Comments

lifeson Over a year ago

That's what I have tried but it does not run. What do you mean by 'reference marker'? (Told you I was new to this!

skparwal Over a year ago

like you have reference id in reviews table. same you should have in image table

lifeson Over a year ago

The ID in tblImages is the ID of the marker (does it matter they are the same name [ID], whereas in tblReviews the tblmarker ID is 'linked' to marker_id in tblReviews

skparwal Over a year ago

as per your comment, above query will work. Please check it and let me know.

Collectives™ on Stack Overflow

MySQL join multiple tables?

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related