Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25

    my query is only returning single values

    hi everyone

    my sql query to a mySQL database is returning incorrect values and i was wondering whehter somone could give me advice on where i went wrong

    basically there are 3 tables ;


    1. u.user - the table of the members of the site i.e the user_id of members.
    2. col- countrylist- this is a list of every country in the world. i.e the name of country and the country_id
    3. cl. countrylocation - this is the link between the users and countrylist table. i.e the user_id and the country_id

    i now need to draft an SQL query that will enable me to obtain the user_id of each member and also obtain information on what country they are located in.

    my query is below


    PHP Code:

    SELECT 
                     u
    .user_id
                      
    u.first_name
                      
    cl.country_id,
                      
    col.country
                          
     
      FROM 
                              users u  
                     LEFT OUTER  JOIN   countrylocation cl
                        ON u
    .user_id cl.country_id
                      LEFT OUTER  JOIN  countrylist col  
            ON cl
    .country_id col.country_id 
            
                GROUP
                    BY  u
    .user_id 

    The problem with the query above is that it only shows once instance of each countyr location.

    For example, i have over 30 members who live in the United States, but the query only shows the country location for the first person who lives in the States; it returns a null value for all other residents living in the States.

    Does anyone have any idea where i have gone wrong with this query.

    warm regards

    Andreea

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,569
    not knowing the table design but do you really mean
    Code:
    ON u.user_id = cl.country_id
    ..that suggests the user.id is synominous with the country_id. I would have expected you to have a countryID in users
    as to why you'd need another join to countrylist I dunno
    perhaps if you posted the table design here it might help
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    hi healdem

    i enclose my table design. i think it will be clearer if you look at my design.

    PHP Code:

     
      CREATE TABLE users
    (<br />
      
    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' ),


        
    etc

    i did not place the countryID in users because i have over 60 similar table 
    and wanted to be able to search on all the valuesi felt that it wold great too many columns in the User table if i had all the values thereso i used a table that act as "link" table between the user and the Country table

    i hope its clear 

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andreea115 View Post
    Does anyone have any idea where i have gone wrong with this query.
    your GROUP BY clause is wrong

    remove it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Hello R937

    thank you for your helpful answer. i did try removing the GROUP BY CLAUSE before writing to the forumn . but when i dd this it produced multiple false results; i.e one user id would then have several instances of the same country.Below is a sample of the incorrect results from the MySQL

    user_id first_name country_id country
    1 A6 1 United States
    1 A6 1 United States
    1 A6 1 United States
    1 A6 1 United States

    i then tried using DISTINCT with my query but this also produced false results i.e

    PHP Code:
    SELECT DISTINCT (u.user_id), u.first_namecl.country_idcol.country
    FROM users u
    LEFT OUTER JOIN countrylocation cl ON u
    .user_id cl.country_id
    LEFT OUTER JOIN countrylist col ON cl
    .country_id col.country_id 
    This produced the same results as the GROUP BY CLAUSE


    Does anyone please have any suggestions as to how this query is suppose to be correctly written. i really am at a complete loss as to where i have gone wrong.

    warm regards

    Andreea

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andreea115 View Post
    PHP Code:
    SELECT DISTINCT (u.user_id), u.first_namecl.country_idcol.country
    FROM users u
    LEFT OUTER JOIN countrylocation cl ON u
    .user_id cl.country_id
    LEFT OUTER JOIN countrylist col ON cl
    .country_id col.country_id 
    two comments

    first, DISTINCT is ~not~ a function, so putting the first column that comes after it into parentheses does not affect how DISTINCT actually works

    second, the reason you're getting bad results is because of the bad join -- u.user_id = cl.country_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Hi R937

    Thank you very for your kind response.

    i am a bit confused, why is this a bad join:

    u.user_id = cl.country_id

    i mean, as far as i can see, i am trying to find out what country of resident the user lives in. So, for example, if i wish to derterimine what country of residence user_id 1 lives in, should i not do a join with

    the countrylocation cl table and then get the country_id
    once i have the country_id should i not then go to the countrylist table and get the name of the countyr.

    if this approah is wrong, please advise me of why it's wrong and of the correct approach. i am keen to master SQL and obviosuly seem to misunderstand a lot of the concepts.

    i look forward to your response.

    warm regards


    Andreea

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andreea115 View Post
    i am a bit confused, why is this a bad join:

    u.user_id = cl.country_id
    because userid 2 will match all countrylocation rows for country 2

    think about it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Hello R937

    I am so sorry but i really dont understand your explanation.

    why would userid 2 match

    all countrylocation rows for country 2

    i am sorry to trouble you on this, but i really dont understand. i am even more confused now than i was before.

    is it possible for u to tell me how it should be done

    warm regards

    Andreea 115

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you give up far too easily

    go to your user table and pull out the row for userid=2

    then go to the countrylocation table and pull out all the rows for countryid=2

    compare them

    these are the rows that your join produces

    you want your query to return the (presumably single) country for each user

    instead, you are joining each user to all the countries that match the countryid to the userid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Hello R937

    Wow!! i think i got it.

    I beleive that this is the correct code; i think it was a sily mistake on my part.

    PHP Code:

    SELECT u
    .user_idu.first_namecl.country_idcol.country
    FROM users u
    LEFT OUTER JOIN countrylocation cl ON u
    .user_id cl.user_id
    LEFT OUTER JOIN countrylist col ON cl
    .country_id col.country_id 

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,569
    As I understand your tables
    Users contains details of users
    CountryList contains details of countries
    CountryLocation associates a specific user with a specific country

    so to get the name of the user and the name of the country they live in you need to join all 3 tables

    Join Users to CountryLocation (on the userid) to find the country that user is in
    then join CountryLocation to CountryList (on the countryID) to find the name of that country

    but this design seems flawed to me, you are not doing anything with CountryLocation, indeed I suspect its logically wrong as it allows a user to be associated with many countries (buyt there is no qualifier indicating when that user was /is associated with that country. I suspect you could drop the table CountryLocation and move the country that a user is associated with to the users table.

    the all you need do is a single join between users and CountryList on the countryID

    I'd also suggest you change the name of the tables to be clearer as to what they contain

    eg instead of CountryList, call it, say Countries.. as it contains details of countries. mind you I'd also be a bit sneaky and use the ISO 3 digit code for the country as its PK

    I would have expected the MembershipType to be a foreign key to another table which qualified the membership types. Being dull I'd probably call that MebershipTypes.
    Last edited by healdem; 12-11-12 at 12:29.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Helllo healdem

    thank you so much for taking the time to respond to this query.

    i read your points; i am still a bit confused by database designs and would love to run this by you.

    The reason why i structed the tables in this way is that i have over 60 tables in the database :

    Many of the table involve

    one to one relationships


    i understand your point that these could be reduced into one to one tables without the need for a link table.

    However, i do have lots of other tables that do involve
    one to many relationships


    For example, i have a spoken languages table in which one user might be listed as speaking several diffrent langauges ; hence 1 user_id but several languauge_ids.

    consequetnly, i assumed that the best approach was to keep the User table free of detailed information and to them use link tables for everything.

    i would really appriicate any advice on the best way to proceed.

    warm regards

    Andreea

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,569
    if you need a one to many relationship, or a many to many relationship then use that.
    but on your present table design you don't do anything with the user location. in the current design you could have many locations for a user, yet there is no validaity for the that location. if you stored, say when a user was in a location then fine. but you dont'. so its a redundant design.

    by storing the countryid within the user you have a one to many relationship (a user has one (current) location, there can be many users with the same country).

    A many to many relationship is often modelled as 3 tables (the two parent tables and an intersection table). often an indtersection table contains just the primary keys of the parent tables (there could be more than two parent tables)

    And incidentally the first reference to your bad join was back in post #2
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Hello again everyone and R937 and Healdem,

    Thank you for your help so far.

    I have two follow on questions ;

    1.
    your GROUP BY clause is wrong
    remove it
    12-10-12 21:27
    R937 advised me to remove my GROUP BY CLAUSE in my query but when i removed the Group BY CLAUSE it produce only one entry.

    so, i am unclear, how to correcctly draft my query.


    The second question was raised as a seperate query-it relates to queries invovling 1-1 relationships and 1 to many:

    http://www.dbforums.com/mysql/168810...ml#post6576957

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
  •