Help with my sql query

hello everyone

i am fairly new to SQL and still a bit confused regarding some of its concepts. So, i would gratefully appricate some advice.

i will be using a mySQL database.

i have a member's database comprising several tables .i.e

1. user table ( the table of users).
   
2. languagelist ( the table containing the list of langauges and their respectives ids).
   
3. the spoken_ languages table ( the link between the languagelist and the user table
   

i.e the spoken_ languages table gives the id of the language spoken plus the Id of the member who speaks that language. i.e



i know how to write an SQL query to extract the list of all members who speak a particular language. i.e



the above query , if i am correct, will give me all the Id of members who speak the language with a language_id of 1.

MY CONFUSION HOWEVER IS THIS;

how can i add to the same query, a search for a full list of all other languages spoken by the selected members.

i.e each member has a profile page that contains information on each langauage spoken by them.

i need to extract this list and add it to the query.

i am unsure how to draft the sql query to extract a list of the langauges spoken by them. i.e i suspect that i will first have to;

get the language Id of each language spoken by the selected member

then run another query to the languagelist table to find out what the language is. i.e language Id 1 = English.

please advise me on the correct procedure.

i hope my question is clear.

warm regards

Andreea

FYI i've moved your thread to the mysql forum

Hello again everyone.

thank you so much to r937 for his responce.

i think i am clear on what was done. i just have one question.



i am unclear of the purpose of the following AND clause



could someone please explain to me what it does or what is its design to prevent; i note its a "not equal to comparism"

thank you so much everyone for your kind attention.

i learnt a lot from this assistance.

warm regards

Andreea

that AND condition is really easy to understand

you start off by picking only those users who speak language 1

then for each of them, you want to know what other languages they speak

the word "other" is the key

Hello again

i would first like to thank r937 for his help thus far.the sql code that was given by him works perfectly.

However when i tried to repeat the same process with another set of database tables it produce an error. i am unclear where i have gone wrong;

i have enclosed my code below the tables are similar to the language tables aauge gar;

//the tables

//the table below is the link table.i.e the profile id and the profile name.

USE workers;
CREATE TABLE workers_general_profiles_list(
profile_id MEDIUMINT UNSIGNED NOT NULL,
profile VARCHAR(60) NOT NULL,
INDEX profile_id (profile_id , profile),
INDEX profile( profile , profile_id ),
UNIQUE KEY (profile ) );



//the table below is the link between the table above and the users table.

USE workers;
CREATE TABLE workers_general_profiles(
profile_id MEDIUMINT UNSIGNED NOT NULL,
user_id MEDIUMINT UNSIGNED NOT NULL,
INDEX user_id (user_id , profile_id),
INDEX profile_id( profile_id , user_id )

);

//below is the full sql code;



$select = " SELECT
users.first_name ,
GROUP_CONCAT(thatlang.language) AS other_languages,
GROUP_CONCAT( wgi.profile)As gen_profile

" ;


$from = " FROM
workers_languagelist as thislangspoken

INNER
JOIN users
ON users.user_id = thislangspoken.user_id
LEFT OUTER
JOIN workers_languagelist AS thatlangspoken
ON thatlangspoken.user_id = users.user_id
AND thatlangspoken.language_id <> thislangspoken.language_id
LEFT OUTER
JOIN languagelist AS thatlang
ON thatlang.language_id = thatlangspoken.language_id


LEFT OUTER JOIN
workers_general_profiles as workersprofile
on workersprofile.user_id = users.user_id
LEFT OUTER JOIN workers_general_profiles_list wgi
ON wgi.profile_id = workersprofile.profile_id


";



/**
$where = sprintf(" WHERE
u.user_id = '%s' ",
mysqli_real_escape_string($dbc,$user_id)

);

**/



$where = " WHERE thislangspoken.language_id = 18 ";





//$order= "ORDER BY u.pic_no DESC, u.user_id DESC LIMIT $start, $display ";




$GROUPBY = " GROUP
BY users.first_name ";

$order= " ORDER BY users.first_name DESC ";



//below is the results from the query

the query simply repeated the number 41+ over and over again.

Korean ,Spanish ,Dutch ,Spanish ,Italian ,Lithuanian ,Croatian ,French ,Japanese ,Spanish ,Danish ,German ,Italian ,Korean ,Croatian ,Dutch ,Spanish ,Japanese ,Lithuanian ,Danish ,German ,French and the profile is: female,41+ years old,female,female,41+ years old,female,41+ years old,female,41+ years old,female,41+ years old,41+ years old,female,41+ years old,female,41+ years old,41+ years old,female,41+ years old,female,female,41+ years old

i cannot debug a query by looking at the php that constructs it

please echo the entire sql string before you execute it

and if it "produces an error" then i need to see the exact error message too

hello again everyone

and special thanks to r937 for his continued assistance. i have been asked to echo my entire sql code inorder to debug the problem.

i have enclosed it below. i have first echoed the sql code and then enclosed the tables that were used in the query.




i hope this is helpful, please let me know if you need anything else.

warm regards

Andreea

what was the error?

Perhaps there's no `users` table!?!

Hello again everyone

and once again my i thank R937 for his contiued assistance and also to caffelette for joining the dicussion.

there is indeed a user table, i did not enclose it in the query. i have now enclosed it below.

i have been asked to describe the error report that i received from my SQL query.

i did not recieve an error report, i did however receive a false/erronious report.

Please note taht when i just used the SQL code given to me by R937 the report was correct. the problem arises when i add additional queries to teh SQL query.

below is a copy of the results of the query .




NAME: A4 LANGUSTES SPOKEN: Bulgarian ,Italian ,Bavarian ,German ,Bangala ,Spanish THE PROFILE:

NAME: A14 LANGUSTES SPOKEN: Bahasa Malaysia ,Italian ,Braille ,French ,Bavarian ,Spanish ,Bulgarian THE PROFILE:


NAME: A LANGUSTES SPOKEN: Italian ,Korean ,Croatian ,Dutch ,Spanish ,Japanese ,Lithuanian ,Danish ,German ,French ,Korean ,Spanish ,Dutch ,Spanish ,Italian ,Lithuanian ,Croatian ,French ,Japanese ,Spanish ,Danish ,German THE PROFILE: female,41+ years old,female,41+ years old,41+ years old,female,41+ years old,female,female,41+ years old,female,41+ years old,female,female,41+ years old,female,41+ years old,female,41+ years old,female,41+ years old,41+ years old



please note . i have highlighted the name of the result set. the result set " THE PROFILE " should be the result set from my addition to the query. however, as u will notice above, the sql query simply repeated the following; " female,41+ years old "

it should however have return lots of other information for each of the 3 users.

you keep using that word "error"

i do not think it means what you think it means

perhaps you shouldn't have expanded the query i gave you without understanding how it works

hi r937.

i thought i understood it. you will note that i have pretty much replicated the format with the expansion. so, i am quite mistified as to why the query is now producing an incorrect result set.

do you have any idea's why its doing this.

warm regards

Andreea

cross join effects

suppose each worker has multiple hats, in a one-to-many relationship

also, suppose each worker has multiple coats, in a separate one-to-many relationship

let's say worker fred has 3 hats and 4 coats

when you combine two unrelated one-to-many relationships in the same query, you get cross join effects

fred has 3 hats and 4 coats, and combining them in the same query gives 12 intermediate rows

ah R937.

thank you so much for explaining this to me; i obviously still have a lot to learn in SQL (but i like the subject very much and am prepared to study up on it).

i will now have to read up on that and try and find a way around it. do you happen to know any articles/tutorials that i can read up on this topic. i very much want to learn how to do it myself.

warm regards

Andreea