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 > my query is only returning single values

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,654
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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 
Reply With Quote
  #4 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #10 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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 
Reply With Quote
  #12 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,654
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.
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton

Last edited by healdem; 12-11-12 at 11:29.
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #14 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,654
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
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.
Quote:
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:

help with query for 1-1 and 1 to many table
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