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,644 | office | Nassau | 895000 | aaaa.gif |
565735 | Commercial Building at The Balmoral | 718,644 | building | Nassau | 895000 | bbbb.gif |
565735 | Commercial Building at The Balmoral | 718,644 | building | Nassau | 895000 | cccc.gif |
565735 | Commercial Building at The Balmoral | 718,644 | building | Nassau | 895000 | dddd.gif |
565735 | Commercial Building at The Balmoral | 718,644 | building | Nassau | 895000 | eeee.gif |
565957 | El Greco Hotel on West Bay Street | 644,649 | building | Nassau | 2950000 | qqqq.jpg |
565957 | El Greco Hotel on West Bay Street | 644,649 | business | Nassau | 2950000 | rrrr.jpg |
565957 | El Greco Hotel on West Bay Street | 644,649 | business | Nassau | 2950000 | ssss.jpg |
565957 | El Greco Hotel on West Bay Street | 644,649 | business | Nassau | 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,644 | office | Nassau | 895000 | aaaa.gif |
565957 | El Greco Hotel on West Bay Street | 644,649 | building | Nassau | 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 Balmoral | office,building | Nassau | 895000 | aaaa.gif |
565957 | El Greco Hotel on West Bay Street | building,business | Nassau | 2950000 | qqqq.jpg |