If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > help with query for 1-1 and 1 to many table

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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:

my query is only returning single values

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' ), 
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
Reply

Tags
mysql

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On