#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3

    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

    PHP Code:
    USE members ;
    CREATE TABLE spoken_languages(
    language_id MEDIUMINT UNSIGNED NOT NULL
    user_id MEDIUMINT UNSIGNED NOT NULL,
    INDEX language_idlanguage_iduser_id ), 
    INDEX user_id user_id,language_id


    ); 
    the language_id column is the Id of the language (derived from the the languagelist table 

    the user_id column is the id of the member drawn from the user
    table

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

    PHP Code:
       
          $lang    
    =     mysql_query("SELECT id  FROM spoken_languages WHERE language_id='1' ORDER BY id ASC"); 
    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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Code:
    SELECT users.username
         , GROUP_CONCAT(thatlang.languagename) AS other_languages
      FROM spoken_languages AS thislangspoken
    INNER
      JOIN users
        ON users.user_id = thislangspoken.user_id
    LEFT OUTER
      JOIN spoken_languages AS thatlangspoken      
        ON thatlangspoken.user_id = users.user_id
       AND thatlangspoken.language_id <> thislangspoken.language_id
    LEFT OUTER
      JOIN languages AS thatlang
        ON thatlang.language_id = thatlangspoken.language_id   
     WHERE thislangspoken.language_id = 1
    GROUP
        BY users.username
    FYI i've moved your thread to the mysql forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3
    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.

    PHP Code:
    LEFT OUTER
      JOIN spoken_languages 
    AS thatlangspoken      
        ON thatlangspoken
    .user_id users.user_id
       
    AND thatlangspoken.language_id <> thislangspoken.language_id 
    i am unclear of the purpose of the following AND clause

    PHP Code:
     AND thatlangspoken.language_id <> thislangspoken.language_id 
    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


    Originally Posted by r937
    Code:
    SELECT users.username
         , GROUP_CONCAT(thatlang.languagename) AS other_languages
      FROM spoken_languages AS thislangspoken
    INNER
      JOIN users
        ON users.user_id = thislangspoken.user_id
    LEFT OUTER
      JOIN spoken_languages AS thatlangspoken      
        ON thatlangspoken.user_id = users.user_id
       AND thatlangspoken.language_id <> thislangspoken.language_id
    LEFT OUTER
      JOIN languages AS thatlang
        ON thatlang.language_id = thatlangspoken.language_id   
     WHERE thislangspoken.language_id = 1
    GROUP
        BY users.username
    FYI i've moved your thread to the mysql forum
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3
    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
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3

    Smile


    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.

    PHP Code:
        
    function the_worker_profile (     )
        
        
         {             
                 global 
    $dbc;
                          
        
        
    $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  "   WHERE             thislangspoken.language_id = 18 "

            
    $GROUPBY   "     GROUP
                    BY  users.first_name "
    ;
                           
                  
    $order=  "     ORDER BY  users.first_name DESC  ";  
             
            
    $query $select.$from.$where$GROUPBY$order ;
             
             
    $result = @mysqli_query ($dbc$query)or trigger_error("Query: $query\n<br />MySQL Error: " mysqli_error($dbc))    ;
            
             return 
    $result 
             
             
        }

    //i now enclose the tables that went into this query

    /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(60NOT NULL,
    INDEX profile_id (profile_id profile),
    INDEX profileprofile 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_idprofile_id user_id )

    );

    //the tables below are the lanauge tables, this part of the query works

    USE workers ;
    CREATE TABLE languagelist(
    language_id MEDIUMINT UNSIGNED NOT NULL
    language VARCHAR(60NOT NULL,
    INDEX language_id language_idlanguage ), 
    INDEX languagelist (languagelanguage_id ), 
    UNIQUE KEY (language)


    ); 



    USE 
    workers ;
    CREATE TABLE workers_languagelist(
    language_id MEDIUMINT UNSIGNED NOT NULL
    user_id MEDIUMINT UNSIGNED NOT NULL,
    INDEX language_idlanguage_iduser_id ), 
    INDEX user_id user_id,language_id


    ); 

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

    warm regards

    Andreea
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    what was the error?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Perhaps there's no `users` table!?!
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3
    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.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by andreea115
    i did not recieve an error report, i did however receive a false/erronious report.
    you keep using that word "error"

    i do not think it means what you think it means

    Originally Posted by andreea115
    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.
    perhaps you shouldn't have expanded the query i gave you without understanding how it works

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3
    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



    Originally Posted by r937
    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

  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by andreea115
    do you have any idea's why its doing this.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3
    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




    Originally Posted by r937
    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

IMN logo majestic logo threadwatch logo seochat tools logo