The old bad way, using csv list of typd IDs in the type field

listing_id property_name oldtype island price filename
565735 Commercial Building at The Balmoral 718,644 Nassau 895000 aaaa.gif
565957 El Greco Hotel on West Bay Street 644,649 Nassau 2950000 qqqq.jpg

Now with the cross reference table and using inner join below.

I'm first inner joining my media table to make sure the record has at least 1 photo. Then I join my islands table to get the name of the island.

My cross reference table has just building_id and type_id. I join that with the buildings table and the type category table to get the text value for the type ids.

The way joins work, when I join my media table, there are multiple instances of each Building row because each building has at least 2 photos. Even if I remove the media table join, there's still multiple instances because each building has multiple categories.

listing_id property_name oldtype newtype island price filename
565735 Commercial Building at The Balmoral 718,644office Nassau 895000 aaaa.gif
565735 Commercial Building at The Balmoral 718,644building Nassau 895000 bbbb.gif
565735 Commercial Building at The Balmoral 718,644building Nassau 895000 cccc.gif
565735 Commercial Building at The Balmoral 718,644building Nassau 895000 dddd.gif
565735 Commercial Building at The Balmoral 718,644building Nassau 895000 eeee.gif
565957 El Greco Hotel on West Bay Street 644,649 buildingNassau 2950000 qqqq.jpg
565957 El Greco Hotel on West Bay Street 644,649 businessNassau 2950000 rrrr.jpg
565957 El Greco Hotel on West Bay Street 644,649 businessNassau 2950000 ssss.jpg
565957 El Greco Hotel on West Bay Street 644,649 businessNassau 2950000 tttt.jpg

To get rid of the duplicates (caused by joining the media table) I GROUP BY listing_id which gives me:

listing_id property_name oldtype newtype island price filename
565735 Commercial Building at The Balmoral 718,644office Nassau 895000 aaaa.gif
565957 El Greco Hotel on West Bay Street 644,649 buildingNassau 2950000 qqqq.jpg

However, now I only have 1 of the 2 type categories that are assigned to each building.

What I would like to have:

listing_id property_name newtype island price filename
565735 Commercial Building at The Balmoraloffice,building Nassau 895000 aaaa.gif
565957 El Greco Hotel on West Bay Street building,businessNassau 2950000 qqqq.jpg