Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25

    help with query for 1-1 and 1 to many table

    Hi Everyone,


    This question follows on from the resolution of an early question in:

    http://www.dbforums.com/mysql/168804...ml#post6576815

    Speical thanks to R937 and healdem


    My question now relates to how to correctly draft a query from a table that has two types of relationships: 1-1 relationship , 1 to many.

    It will be easier if i explain the table design.

    It's a members database containing over 60 individual tables relating to it's members profiles; i.e languagage skills, education etc;

    so a member might have numrous language skills (1 to many), visited numrous countries (1 to many) but now lives in 1 country ( 1-1).

    There are 3 types of tables ;


    1. User- the member's table i.e containing the user_id of members.

    2. Data Tables- table containing data lists i.e list of countries, list of qualifications etc
    3. Link Table - this is the link between the users and data tables. i.e the id of the user linked to the Id of the datatable entry.


    I now want to build an SQL query to search the database. Below is a part sample of my query:



    PHP Code:
               
       SELECT 
           u
    .user_id
           
    u.first_name,
           (
    wl.language_id)fleuntlang,  
           (
    wml.language_id)mediumlang,
           (
    wag.age_id)as ageid
           ( 
    wc.job_id)as jobid
       FROM 
                users u            
                           
                    LEFT OUTER JOIN  members_timetable mt
                        ON u
    .user_id mt.user_id             
                LEFT OUTER JOIN  workers_languagelist wl
                        ON u
    .user_id wl.user_id              
                LEFT OUTER JOIN  workers_medium_languagelist wml
                        ON u
    .user_id wml.user_id                
                LEFT OUTER JOIN workers_agegroup wag
                        ON u
    .user_id wag.user_id          
                LEFT OUTER JOIN  worker_category wc
                        ON u
    .user_id wc.user_id 
    the problem with the way i have structured the query is that it is leading to duplicate results.

    for example , if user_id 1 speak 6 languauges, has 9 diffrent skills sets, and have visited 20 countries then the result will produce

    20 x 6 instances of the languauge Id plus
    9 x 6 instnace of the langauge id etc

    this leads to thousand of results which slows my query down to a snails pace.

    i am unclear where i have gone wrong.

    i tried using GROUP_CONCAT along with GROUP BY but this also produced false results.
    PHP Code:

    SELECT 
                     u
    .user_id
                      
    u.first_name,
                  
                
    GROUP_CONCATwl.language_id)fleuntlang,  
                  
    GROUP_CONCAT(wml.language_id)mediumlang,
                
    GROUP_CONCATwag.age_id)as ageid
                
    GROUP_CONCATwc.job_id)as jobid
                   FROM 
                              users u            
                           
     LEFT OUTER JOIN  members_timetable mt
                        ON u
    .user_id mt.user_id   ......          
    .................

                 
           
    GROUP
                    BY  u
    .user_id,  wl.language_idwml.language_id,  wc.job_id 
    Below is a sample of the database tables. they are all pretty much like this. i.e a table listing the qualifications-along with its Id and then a link table linking the user to the list.

    i would really appriciate any advice assitance on how to proceed



    PHP Code:


      CREATE TABLE users
    (
      
    user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
      
    membership_type CHAR(3NOT NULL

    PRIMARY KEY (user_id)

    }

     
    CREATE TABLE countrylist
      country_id  MEDIUMINT UNSIGNED NOT NULL

      
    country    VARCHAR(60NOT NULL,
      
    INDEX country_id country_id,  country  ), 
      
    INDEX countrylist (countrycountry_id ), 
      
    UNIQUE KEY (country)


      
    CREATE TABLE countrylocation(
      
    country_id   VARCHAR(60NOT NULL,
      
    user_id  MEDIUMINT UNSIGNED NOT NULL,
      
    INDEX country_id (country_iduser_id ), 
      
    INDEX user_id (user_idcountry_id  )
    ); 

    The values in teh countrylist 


        
    ('African' ),
        (
    ' Albanian' ),
        (
    'Saudia Arabia ' ),
        (
    ' Armenia' ),
        (
    ' Australia' ),
        (
    ' Austria' ), 

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    for each 1-to-many relationship, do the GROUP_CONCAT in a subquery in the FROM clause

    example --
    Code:
    SELECT u.user_id
         , u.first_name
         , wl.fluentlanguages
         , wml.mediumlanguages
         , ...
      FROM users AS u
    LEFT OUTER
      JOIN ( SELECT userid
                  , GROUP_CONCAT(language_id) AS fluentlanguages
               FROM workers_languagelist
             GROUP
                 BY userid ) AS wl
        ON wl.userid = u.userid
    LEFT OUTER
      JOIN ( SELECT userid
                  , GROUP_CONCAT(language_id) AS mediumlanguages
               FROM workers_medium_languagelist 
             GROUP
                 BY userid ) AS wml
        ON wml.userid = u.userid
    ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •