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

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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:
wag.age_id)as ageid
wc.job_id)as jobid
            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:

GROUP_CONCATwag.age_id)as ageid
GROUP_CONCATwc.job_id)as jobid
                          users u            
 LEFT OUTER JOIN  members_timetable mt
                    ON u
.user_id mt.user_id   ......          

                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:

membership_type CHAR(3NOT NULL

PRIMARY KEY (user_id)


CREATE TABLE countrylist

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,
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)  
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 --
SELECT u.user_id
     , u.first_name
     , wl.fluentlanguages
     , wml.mediumlanguages
     , ...
  FROM users AS u
  JOIN ( SELECT userid
              , GROUP_CONCAT(language_id) AS fluentlanguages
           FROM workers_languagelist
             BY userid ) AS wl
    ON wl.userid = u.userid
  JOIN ( SELECT userid
              , GROUP_CONCAT(language_id) AS mediumlanguages
           FROM workers_medium_languagelist 
             BY userid ) AS wml
    ON wml.userid = u.userid
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote


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