Combine 2 queries into one

[lurtnowski]

I have 3 tables (amenities, properties, amenities_properties)
This query prints out all the amenities

SELECT name FROM amenities

This query prints out only the amenities for a property

SELECT amenity FROM properties_amenities WHERE propertyID=1

How can I combine the queries so that all the amenities so that the amenities of the property are marked or something?

[r937]

SELECT amenities.name , CASE WHEN properties_amenities.propertyID = 1 THEN 'Y' ELSE ' ' END AS property_1_amenity FROM amenities LEFT OUTER JOIN properties_amenities ON properties_amenities.amenityID = amenities.amenityID AND properties_amenities.propertyID = 1

[lurtnowski]

ok, when I open up phpmyadmin, and enter the query, i get

I changed the column name to compare the name of the amenity

SELECT amenities.name
     , CASE WHEN properties_amenities.propertyID = 1
            THEN 'Y'
            ELSE ' ' END   AS property_1_amenity
  FROM amenities
LEFT OUTER
  JOIN properties_amenities 
    ON properties_amenities.amenity= amenities.name
   AND properties_amenities.propertyID = 1

which worked!

Is that ok?

[r937]

@lurtnowski

which worked!

i love it when that happens