Database:
Expand|Select|Wrap|Line Numbers
- CREATE TABLE properties (
- property_id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
- property_name VARCHAR(36),
- PRIMARY KEY (property_id)
- );
- CREATE TABLE residences (
- residence_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
- property_id TINYINT(3) UNSIGNED NOT NULL,
- residence_floor TINYINT(3) UNSIGNED NOT NULL,
- residence_line VARCHAR(2) NOT NULL,
- residence_type ENUM('studio','alcove','1 bedroom','2 bedroom','3 bedroom'),
- residence_bathrooms VARCHAR(3) NOT NULL,
- residence_rent DECIMAL(6,2),
- residence_availabilitydate DATE,
- residence_status ENUM('off','on'),
- PRIMARY KEY (residence_id)
- );
- CREATE TABLE features (
- feature_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
- property_id TINYINT(3) UNSIGNED NOT NULL,
- feature_name VARCHAR(36),
- PRIMARY KEY (feature_id)
- );
- CREATE TABLE residences_x_features (
- residence_id SMALLINT(5) UNSIGNED NOT NULL,
- feature_id SMALLINT(5) UNSIGNED NOT NULL,
- PRIMARY KEY (residence_id,feature_id)
- );
- CREATE TABLE incentives (
- incentive_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
- property_id TINYINT(3) UNSIGNED NOT NULL,
- incentive_name VARCHAR(36),
- PRIMARY KEY (incentive_id)
- );
- CREATE TABLE residences_x_incentives (
- residence_id SMALLINT(5) UNSIGNED NOT NULL,
- incentive_id SMALLINT(5) UNSIGNED NOT NULL,
- PRIMARY KEY (residence_id,incentive_id)
- );
- CREATE TABLE floorplans (
- floorplan_id MEDIUMINT(7) UNSIGNED NOT NULL AUTO_INCREMENT,
- residence_id SMALLINT(5) UNSIGNED NOT NULL,
- floorplan_jpg VARCHAR(36),
- floorplan_pdf VARCHAR(36),
- floorplan_label VARCHAR(48),
- PRIMARY KEY (floorplan_id)
- );
- CREATE TABLE views (
- view_id MEDIUMINT(7) UNSIGNED NOT NULL AUTO_INCREMENT,
- residence_id SMALLINT(5) UNSIGNED NOT NULL,
- view_jpg VARCHAR(36),
- view_label VARCHAR(48),
- PRIMARY KEY (view_id)
- );
Expand|Select|Wrap|Line Numbers
- SELECT
- residences.residence_id,residences.residence_floor,residences.residence_line,residences.residence_type,residences.residence_bathrooms,residences.residence_availabilitydate,residences.residence_rent,residences.residence_status,
- GROUP_CONCAT(DISTINCT floorplans.floorplan_label ORDER BY floorplans.floorplan_id) AS floorplan_labels,
- GROUP_CONCAT(floorplans.floorplan_pdf ORDER BY floorplans.floorplan_id) AS floorplan_pdfs,
- GROUP_CONCAT(floorplans.floorplan_jpg ORDER BY floorplans.floorplan_id) AS floorplan_jpgs,
- GROUP_CONCAT(DISTINCT views.view_jpg) AS view_jpgs,
- GROUP_CONCAT(DISTINCT views.view_label) AS view_labels,
- GROUP_CONCAT(DISTINCT features.feature_name) AS featurelist,
- GROUP_CONCAT(DISTINCT incentives.incentive_name) AS incentivelist
- FROM residences
- LEFT JOIN floorplans ON residences.residence_id = floorplans.residence_id
- LEFT JOIN views ON views.residence_id = residences.residence_id
- LEFT JOIN residences_x_features ON residences_x_features.residence_id = residences.residence_id
- LEFT JOIN features ON features.feature_id = residences_x_features.feature_id
- LEFT JOIN residences_x_incentives ON residences_x_incentives.residence_id = residences.residence_id
- LEFT JOIN incentives ON incentives.incentive_id = residences_x_incentives.incentive_id
- WHERE residences.property_id = 1
- GROUP BY residences.residence_id
- ORDER BY residences.residence_floor DESC,residences.residence_line ASC
Expand|Select|Wrap|Line Numbers
- +--------------+------------------------------------------------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------+--------------------+-------------------+---------------+
- | residence_id | floorplan_labels | floorplan_pdfs | floorplan_jpgs | view_jpgs | view_labels | featurelist | incentivelist |
- +--------------+------------------------------------------------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------+--------------------+-------------------+---------------+
- | 301 | a,b,c | ,,,,,,6_301_59.pdf,6_301_59.pdf,6_301_59.pdf,6_301_59.pdf,6_301_59.pdf,6_301_59.pdf,,,,,, | 6_301_58.jpg,6_301_58.jpg,6_301_58.jpg,6_301_58.jpg,6_301_58.jpg,6_301_58.jpg,6_301_59.jpg,6_301_59.jpg,6_301_59.jpg,6_301_59.jpg,6_301_59.jpg,6_301_59.jpg,6_301_60.jpg,6_301_60.jpg,6_301_60.jpg,6_301_60.jpg,6_301_60.jpg,6_301_60.jpg | 6_301_47.jpg,6_301_46.jpg,6_301_51.jpg | TEST 2,TEST,TEST 3 | Marble Lobby,test | NULL |
- | 302 | test,Test Floorplan,PDF test,Double Test,x,y,z | ,,,,6_302_41.pdf,6_302_41.pdf,6_302_42.pdf,6_302_42.pdf,,,,,, | 6_302_39.jpg,6_302_39.jpg,6_302_40.jpg,6_302_40.jpg,,,6_302_42.jpg,6_302_42.jpg,6_302_52.jpg,6_302_52.jpg,6_302_53.jpg,6_302_53.jpg,6_302_54.jpg,6_302_54.jpg | 6_302_53.jpg,6_302_52.jpg | y,x | NULL | NULL |