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

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3

    Help with a SQL multi column query


    hello everybody

    i am still a bit confused by SQL queries and very desperate to learn. so i really appriciate your advice on where i have gone wrong with this query.

    i am using a MySQL database.

    i have an employment database table.

    1. table one is the - user Id table - this is the table containing the Id of members.

    2. table two is the list of skills sets.
      i.e
      first degree skills_id 1
      masters degree skills_id 2
      profssional qualifications. skills_id 3

    3. table three is the link between table one and two.

      ie.


      USE workstable ;
      CREATE TABLE skills_list(
      skills_id MEDIUMINT UNSIGNED NOT NULL,
      user_id MEDIUMINT UNSIGNED NOT NULL,
      INDEX skills_id( skills_id, user_id ),
      INDEX user_id ( user_id, skills_id)
      );





    A member might have several entries for the skills list table.

    The problem however is that when i use an SQL query to query the 'skills_list' table i am only getting one result per member.

    it might help at this point if i show you my query;



    PHP Code:


               
                 
    function worker_list $user_id  )  
        
        {
                   global 
    $dbc;
                   
           
    $select " SELECT 
                   u.name,
                            u. date_joined,
                        sk.skills_id,
                        la.languages_id
                        
                        "
    ;
        
                       
                       
         
    $from   " FROM 
                       users u  LEFT  OUTER JOIN skills_list sk 
                          ON u.user_id = sk.user_id
                       users u  LEFT  OUTER JOIN languages_list la 
                          ON u.user_id = la.user_id
                                              "
    ;

                       
         
    $where  sprintf(" WHERE
                   u.user_id = '%s'   "

                       
    mysqli_real_escape_string($dbc,$user_id)
                                      );
            
                        
           
    $query $select.$from.$where  ;
            
    $result mysqli_query ($dbc,  $query) or trigger_error("Query:  $query\n<br />MySQL Error: " mysqli_error($dbc));
        
            
           return 
    $result 
                     
        }


    //please note that the languages list is similar to the skills_list column. i.e  a member can have several entries for all the languages that he speaks 
    the above query should have given me several entries for the users langauge and skills entries; instead it returns only the first entry s for each of the column.

    if however i only query for one item in my query, it produces the correct result; ie.


    PHP Code:


               
    function worker_list $user_id  )  
        
        {
                   global 
    $dbc;
                   
           
    $select " SELECT 
                        skills_id
                        "
    ;
        
                       
                       
         
    $from   " FROM 
                        skills_list 
                                              "
    ;

                       
         
    $where  sprintf(" WHERE
                   u.user_id = '%s'   "

                       
    mysqli_real_escape_string($dbc,$user_id)
                                      );
            
                        
           
    $query $select.$from.$where  ;
            
    $result mysqli_query ($dbc,  $query) or trigger_error("Query:  $query\n<br />MySQL Error: " mysqli_error($dbc));
        
            
           return 
    $result 
                     
        } 
    The above query will return all the entries for a user within the skill set table.

    So i am unclear where i went wrong when i tried to query several tables at the same time; should i have used a 'Group by Clause'. if so, how do i draft it.

    thank you for your kind attention.

    warm regards

    Andreea
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    Your from clause is incorrect

    Code:
      from users u  
      left 
      join skills_list sk 
        on u.user_id = sk.user_id
      left
      join languages_list la 
        on u.user_id = la.user_id
    I'm surprised that you did not get any error.

    I do not know what result you expect to get but probably your current query will not give that result. Could give some sample data and the expected result?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3
    Hello again

    thank you to swampBoogie for his reply.

    i have been asked to give a sample of my data and what i expect to receive. i will explain

    Below is the data in the tables;

    All i am trying to do is to retrieve all the Language_id for the Language_list table and the skills_id for the skills_list table.





    Table: languages_list

    Full Texts language_id user_id
    Edit Delete 52 20
    Edit Delete 53 20
    Edit Delete 54 20
    Edit Delete 55 20
    Edit Delete 56 20
    Edit Delete 57 20





    Table skills_list
    Full Texts skills_id user_id
    Edit Delete 1 20
    Edit Delete 2 20
    Edit Delete 3 20
    Edit Delete 4 20
    Edit Delete 5 20
    Edit Delete 6 20
    Edit Delete 8 20
    Edit Delete 9 20
    Edit Delete 10 20



    the retsults from the query that i performed should have been for me to have two variable:

    $language_id

    the above variable should have the numbers:

    52 53 54 55 56 57

    $skills_id

    the above variable should have the numbers 1 to 10.

    the results returned by my query was just the first number from each of these variables.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    3
    From what I can see everything looks good can you post the updated query without the php

    Edit:
    Try simplifying the code and declaring a specific result for testing, that is usually my first stop on debugging.
    Code:
    SELECT
      u.name
    , u.date_joined
    , sk.skills_id
    , la.languages_id
    
    FROM users AS u
      LEFT
      JOIN skills_list AS sk 
        ON sk.user_id = u.user_id
    
      LEFT
      JOIN languages_list AS la 
        ON u.user_id = la.user_id
    
    WHERE u.user_id = 20

IMN logo majestic logo threadwatch logo seochat tools logo