Multiple JOINS and controlling duplicates

[beargrease]

I'm kind of comfortable with basic joins, but a current project requires a complex query of many tables. The GROUP_CONCAT(DISTINCT ...) function has been very useful as returning my values as comma delimited values when joining multiple tables. I have one table called 'floorplans' which has two fields (floorplan_jpg & floorplan_pdf), I'd like each of these fields to return arrays of the same length (they have the same # of values in the data model). When i use GROUP_CONCAT(DISTINCT...) on these fields it returns the arrays as I'd like except for that it eliminates 'duplicate' empty fields from the result. How do I collect only the unique values from a field, while also collecting multiple empty values?

Database:

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)
);

Query:

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

Results:

+--------------+------------------------------------------------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------+--------------------+-------------------+---------------+
| 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          |
 

Basically, if I add DISTINCT to the floorplan_jpg and floorplan_pdf SELECT, I get close to what I need, except it eliminates duplicate empty values, which i want to maintain. If I remove DISTINCT, I get exponential filename duplicates due to the multiple joins. I've been strugging with this for days, any input would be greatly appreciated. Thanks!

[chaarmann]

What you wrote is very hard to understand.
Can you please:
1.) simplify the problem by leaving out all tables and parts of the SQL that have nothing to do with the problem itself. It's a help for you to focus more on the real problem, and of couse for us so we can understand easily and not waste a long time in doing so.
2.) list some test data that you have in your tables. Without data, nobody can try your problem on his own machine and verify his developed solution.

A general tip:
DISTINCT is not the only way to remove duplicate records from the result. Especially if you want to leave out some columns in your decision about: if two rows are equal or not. This sound like your case.
Usually you use for that purpose:
select min(id) from... group by ...HAVING count(*) > 1
to pick the first one of all duplicate rows that you want to keep, and filter out all the others with ... where exists () ...

[ronverdonk]

This thread was almost hijacked by eviephillips. That problem has been split off and continues as a new thread http://www.thescripts.com/forum/thread778362.html

moderator