Need help with my query

Question

Hi. Sorry for the horrible title, not sure how to explain this. I have a query that looks like this:

SELECT `name`, `latitude`, `longitude`, `indoor`, `address`,
    `phone`, `email`, `website`, `closed`
FROM rocks INNER JOIN 
    (SELECT DISTINCT id FROM rock_types WHERE type="DWS" or type="Top rope")
    AS types ON rocks.id=types.id
WHERE (latitude>-180 AND latitude<180) AND (longitude>-180 AND longitude<180)

In addition to what I'm getting right now, I'd like to get the list of types associated with each id. How can I do this? Thanks

Answer 1

Here is my take using traditional join syntax:

SELECT rocks.name,
       rocks.latitude,
       rocks.longitude,
       rocks.indoor,
       rocks.address,
       rocks.phone,
       rocks.email,
       rocks.website,
       rocks.closed,
       rock_types.type

FROM   rocks,
       rock_types

WHERE  rocks.latitude  > -180                 AND
       rocks.latitude  < 180                  AND
       rocks.longitude > -180                 AND
       rocks.longitude < 180                  AND
       rocks.type_id   = rocks_types.id       AND
       rock_types.type IN ('DWS', 'Top rope')

The rocks.id = rocks_types.id does the link between both tables.

Here is how you can write the same query using INNER JOIN (both leads to the same result)

SELECT rocks.name,
       rocks.latitude,
       rocks.longitude,
       rocks.indoor,
       rocks.address,
       rocks.phone,
       rocks.email,
       rocks.website,
       rocks.closed,
       rock_types.type

FROM   rocks
       INNER JOIN rocks_types ON rocks.type_id = rocks_types.id

WHERE  rocks.latitude  > -180                 AND
       rocks.latitude  < 180                  AND
       rocks.longitude > -180                 AND
       rocks.longitude < 180                  AND
       rock_types.type IN ('DWS', 'Top rope')

EDIT: Based on your comment, the following will give you a comma separated list of types:

SELECT rocks.name,
       rocks.latitude,
       rocks.longitude,
       rocks.indoor,
       rocks.address,
       rocks.phone,
       rocks.email,
       rocks.website,
       rocks.closed,
       (SELECT    GROUP_CONCAT(DISTINCT rock_types.type SEPARATOR ',')
        FROM      rock_types
        WHERE     rock_types.id = rocks.id                AND
                  rock_types.type IN ('DWS', 'Top rope')
        GROUP BY  rock_types.id
       ) AS type

FROM   rocks

WHERE  rocks.latitude  > -180    AND
       rocks.latitude  < 180     AND
       rocks.longitude > -180    AND
       rocks.longitude < 180

Answer 2

SELECT `name`, `latitude`, `longitude`, `indoor`, `address`,
    `phone`, `email`, `website`, `closed`, 
    (SELECT GROUP_CONCAT(type SEPARATOR ',') FROM rock_types 
        WHERE (type="DWS" or type="Top rope") and  rock_types.id=rocks.id group by id) types
FROM rocks 
WHERE (latitude>-180 AND latitude<180) AND (longitude>-180 AND longitude<180)