MySQL 3 table join

Question

I have data to get listings according to their location. The locations are mapped in a location map table. The structure basically looks like:

listings
  -id
  -title
  etc.

locations
  -id
  -location_name
  etc. 

location_map
  -listing_id
  -location_id

When I want to list the listings according to their location, I am using the following SQL. Basically, I have the location id, and I then can find the listings that are in that location. I use the DISTINCT so that I don't get duplicate location results.

This works, but I'm wondering if this is a good way to do it, as I need to use DISTINCT. Is there a better, more preferred way of doing something like this?

SELECT DISTINCT LS.title, LS.id, LS.url, LS.description

        FROM listings LS

            JOIN location_map MAP ON LS.id = MAP.listing_id
            JOIN locations LOC ON LOC.id = MAP.location_id

        WHERE LOC.id = 123

        ORDER BY LS.title;

Answer 1

You could try

SELECT LS.title, LS.id, LS.url, LS.description
FROM listings LS
WHERE 
LS.id IN (
   SELECT LM.listing_id
   FROM location_map LM
   WHERE LM.location_id = 123 )

This eliminates the join to location.

I'm not sure what the DISTINCT problem is. If you've identified a single location, why would there be duplicates?