473,699 Members | 2,278 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Multiple JOINS and controlling duplicates

1 New Member
I'm kind of comfortable with basic joins, but a current project requires a complex query of many tables. The GROUP_CONCAT(DI STINCT ...) 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(DI STINCT...) 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:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE properties (
  2. property_id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT, 
  3. property_name VARCHAR(36),
  4. PRIMARY KEY (property_id)
  5. );
  6.  
  7. CREATE TABLE residences (
  8. residence_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  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. );
  19.  
  20. CREATE TABLE features (
  21. feature_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  22. property_id TINYINT(3) UNSIGNED NOT NULL,
  23. feature_name VARCHAR(36),
  24. PRIMARY KEY (feature_id)
  25. );
  26.  
  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. );
  32.  
  33. CREATE TABLE incentives (
  34. incentive_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  35. property_id TINYINT(3) UNSIGNED NOT NULL,
  36. incentive_name VARCHAR(36),
  37. PRIMARY KEY (incentive_id)
  38. );
  39.  
  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. );
  45.  
  46. CREATE TABLE floorplans (
  47. floorplan_id MEDIUMINT(7) UNSIGNED NOT NULL AUTO_INCREMENT,
  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. );
  54.  
  55. CREATE TABLE views (
  56. view_id MEDIUMINT(7) UNSIGNED NOT NULL AUTO_INCREMENT,
  57. residence_id SMALLINT(5) UNSIGNED NOT NULL,
  58. view_jpg VARCHAR(36),
  59. view_label VARCHAR(48),
  60. PRIMARY KEY (view_id)
  61. );
Query:

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
Results:
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          |
  6.  
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 3163
chaarmann
785 Recognized Expert Contributor
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
ronverdonk
4,258 Recognized Expert Specialist
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
Mar 4 '08 #3

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

Similar topics

1
4658
by: Prem | last post by:
Hi All Database Gurus, I am trying to write code which will produce all the possible valid queries, given tables and join information for tables. Right now i am just trying to construct all the sequential joins. eg. if i have 4 tables A, B, C, D and the join conditions are A Inner join B, B Inner Join C, C Left Outer join D then i am constructing joins as :
1
1936
by: ankush | last post by:
I have a table heirarchy of around 5 tables. At places where i have to do a search with criteria ranging on all these tables, will it be advisable to have a join on all the tables. A couple of tables can run into million of rows. Is it advisable to do an iterative search by joining two tables and going down the heirarchy (which will end up in selecting more and narrowing down with each subsequent level) or is it better to do a join in one go and...
7
6253
by: SeaviewBlue | last post by:
Howdy folks, I am new to the site and new to joins, so I'm hoping I can get some much needed assistance. Using ASP, VBscript & an Access 2003 db, I am building a page to display results from track and field events, and am struggling with joining information from 3 tables. I will try to explain what I have and what I am doing as clearly as possible. Table 1 - Results MeetID - Field used to select records from Results table
2
19719
by: narendra vuradi | last post by:
Hi I have a requirement where in i haev to convert the SQL from Oracle to the one which will run on the SQL server. in the Oracle Query i am doing multiple joins, between some 13 tables. and some of these joins are inner joins and some are Left outer joins. table1 inner joined with table 2 table2 inner join with table3 table2 inner join with table4 table2 left join with table5
2
1772
by: eviephillips | last post by:
I am having a similar problem. I am using Dreamweaver (MySQL, PHP) and their advanced recordset to select the tables and fields. The recordsets are pulling all the pertinent records. However, when a shirt is in two colors and available in 10 sizes, each color repeats 10 times for each size in the dynamic color drop down list, and the 10 sizes are repeated twice for each color in the size drop down list. This is from the DETAIL page...
3
2272
by: modernshoggoth | last post by:
G'day all, I'm trying to update a single table (containing plans for flights) with information from the same and other tables (containing info for organisations and locations). tblFlightPlans contains the txt-column 'TripSummary', which is what I'm trying to update, as well as ID numbers for the origin, destination, and plane carrier (eg, Qantas). The SQL code below successfully joins tblLocations and tblFlightPlans and updates TripSummary...
13
4269
beacon
by: beacon | last post by:
I'm working on creating a dynamic report that is based on a crosstab query. It's similar to the method described at the following link: http://support.microsoft.com/kb/328320 I've tweaked the method above to make the report display how I want, but the underlying query is the part that I'm having trouble with. I know I will need a couple, if not more, queries to create the crosstab, but I'm having trouble getting the select queries to...
0
1722
by: kalinda | last post by:
I am trying to make multiple joins between two tables and am at a loss. I've tried a variety of things with this being the latest, but it pulls all records multiple times. Basically I have a main table of profiles that have several descriptors that are referenced with a SQL ID that is in a DropDown table. Table Profile profiletype (SQL ID) phoneprovider (SQL ID) Table DropDowns ID
3
1948
by: John Roberts | last post by:
Hi, Is it possible to access a tables data over multiple joins? For instance, for each row of Table A, I want the associated data from Table D. Is this possible in access? If it's not, could you suggest an alternative? Please see the example. Many Thanks. SELECT Table A.Person_Name, Table D.Favorite_Sport FROM (Table A LEFT JOIN Table B ON Table A.ID=Table B.ID) LEFT JOIN Table C ON Table B.ID=Table C.ID LEFT JOIN Table D ON Table...
0
8618
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9178
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8885
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7752
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6534
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5875
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4631
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3058
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2010
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.