471,897 Members | 1,539 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,897 software developers and data experts.

Multiple JOINS and controlling duplicates

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?


Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE properties (
  3. property_name VARCHAR(36),
  4. PRIMARY KEY (property_id)
  5. );
  7. CREATE TABLE residences (
  9. property_id TINYINT(3) UNSIGNED NOT NULL,
  10. residence_floor TINYINT(3) UNSIGNED NOT NULL,
  11. residence_line VARCHAR(2) NOT NULL,
  12. residence_type ENUM('studio','alcove','1 bedroom','2 bedroom','3 bedroom'),
  13. residence_bathrooms VARCHAR(3) NOT NULL,
  14. residence_rent DECIMAL(6,2),
  15. residence_availabilitydate DATE,
  16. residence_status ENUM('off','on'),
  17. PRIMARY KEY (residence_id)
  18. );
  20. CREATE TABLE features (
  22. property_id TINYINT(3) UNSIGNED NOT NULL,
  23. feature_name VARCHAR(36),
  24. PRIMARY KEY (feature_id)
  25. );
  27. CREATE TABLE residences_x_features (
  28. residence_id SMALLINT(5) UNSIGNED NOT NULL,
  29. feature_id SMALLINT(5) UNSIGNED NOT NULL,
  30. PRIMARY KEY (residence_id,feature_id)
  31. );
  33. CREATE TABLE incentives (
  35. property_id TINYINT(3) UNSIGNED NOT NULL,
  36. incentive_name VARCHAR(36),
  37. PRIMARY KEY (incentive_id)
  38. );
  40. CREATE TABLE residences_x_incentives (
  41. residence_id SMALLINT(5) UNSIGNED NOT NULL,
  42. incentive_id SMALLINT(5) UNSIGNED NOT NULL,
  43. PRIMARY KEY (residence_id,incentive_id)
  44. );
  46. CREATE TABLE floorplans (
  48. residence_id SMALLINT(5) UNSIGNED NOT NULL,
  49. floorplan_jpg VARCHAR(36),
  50. floorplan_pdf VARCHAR(36),
  51. floorplan_label VARCHAR(48),
  52. PRIMARY KEY (floorplan_id)
  53. );
  55. CREATE TABLE views (
  57. residence_id SMALLINT(5) UNSIGNED NOT NULL,
  58. view_jpg VARCHAR(36),
  59. view_label VARCHAR(48),
  60. PRIMARY KEY (view_id)
  61. );

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. residences.residence_id,residences.residence_floor,residences.residence_line,residences.residence_type,residences.residence_bathrooms,residences.residence_availabilitydate,residences.residence_rent,residences.residence_status, 
  3. GROUP_CONCAT(DISTINCT floorplans.floorplan_label ORDER BY floorplans.floorplan_id) AS floorplan_labels, 
  4. GROUP_CONCAT(floorplans.floorplan_pdf ORDER BY floorplans.floorplan_id) AS floorplan_pdfs, 
  5. GROUP_CONCAT(floorplans.floorplan_jpg ORDER BY floorplans.floorplan_id) AS floorplan_jpgs, 
  6. GROUP_CONCAT(DISTINCT views.view_jpg) AS view_jpgs, 
  7. GROUP_CONCAT(DISTINCT views.view_label) AS view_labels, 
  8. GROUP_CONCAT(DISTINCT features.feature_name) AS featurelist,
  9. GROUP_CONCAT(DISTINCT incentives.incentive_name) AS incentivelist 
  10. FROM residences 
  11. LEFT JOIN floorplans ON residences.residence_id = floorplans.residence_id 
  12. LEFT JOIN views ON views.residence_id = residences.residence_id 
  13. LEFT JOIN residences_x_features ON residences_x_features.residence_id = residences.residence_id 
  14. LEFT JOIN features ON features.feature_id = residences_x_features.feature_id 
  15. LEFT JOIN residences_x_incentives ON residences_x_incentives.residence_id = residences.residence_id 
  16. LEFT JOIN incentives ON incentives.incentive_id = residences_x_incentives.incentive_id 
  17. WHERE residences.property_id = 1 
  18. GROUP BY residences.residence_id 
  19. ORDER BY residences.residence_floor DESC,residences.residence_line ASC
Expand|Select|Wrap|Line Numbers
  1. +--------------+------------------------------------------------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------+--------------------+-------------------+---------------+
  2. | residence_id | floorplan_labels                               | floorplan_pdfs                                                                            | floorplan_jpgs                                                                                                                                                                                                                            | view_jpgs                              | view_labels        | featurelist       | incentivelist |
  3. +--------------+------------------------------------------------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------+--------------------+-------------------+---------------+
  4. |          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          |
  5. |          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!
Feb 27 '08 #1
2 2944
785 Expert 512MB
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 () ...
Feb 28 '08 #2
4,258 Expert 4TB
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

Mar 4 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Prem | last post: by
2 posts views Thread by narendra vuradi | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.