Help with a SQL multi column query

Dev Shed Forums Sponsor:

July 16th, 2012, 01:24 AM

andreea115

Contributing User

Join Date: Apr 2012

Posts: 48

Time spent in forums: 10 h 2 m 36 sec
Reputation Power: 2

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.

table one is the - user Id table - this is the table containing the Id of members.
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
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

July 16th, 2012, 06:10 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,351

Time spent in forums: 1 Month 4 Days 8 h 2 m 22 sec
Reputation Power: 390

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?

July 16th, 2012, 10:27 AM

andreea115

Contributing User

Join Date: Apr 2012

Posts: 48

Time spent in forums: 10 h 2 m 36 sec
Reputation Power: 2

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.

July 16th, 2012, 12:58 PM

deljr

Contributing User

Join Date: Jun 2012

Location: Wisconsin

Posts: 78

Time spent in forums: 1 Day 11 h 51 m 6 sec
Reputation Power: 1

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

Help with a SQL multi column query

Developer Shed Advertisers and Affiliates