CODEWITHSUNDEEP
mysql
Lion Smith
Lion Smith

Reputation: 655

how to select one table then join another table mysql

so i have two tables.

users

users_id   |   firstname    |  lastname
10001      |      mike      |    lapiz
10002      |      tom       |    jerry

profile

profile_id   |   department   |   specialization
10001        |    Health Dept |     Heart
10002        |   Brain Dept   |     Brain

maybe you're curious why i separate the name of the user and the profile.. i have my on reasons for that.. what i wanted to do is to select my all fields from profile then join the users table

what i want to be the result is

users_id | firstname   |  lastname | profile_id  | department   |specialization
10001    |  mike       |   lapiz   |  1001       |  health dept |   heart

this is my query..

$sql = SELECT a.profile_id,a.department,a.specialization FROM `tbl_profile` AS a LEFT JOIN (SELECT users_id,firstname,lastname FROM `tbl_users`) AS b ON a.profile_id = b.users_id

what happen is it only display the profile table.. it is not displaying the other table.. and when i tried to

LEFT JOIN (SELECT b.users_id,b.firstname,b.lastname FROM `tbl_users`) AS b

it give me an error unknown column b.users_id

Upvotes: 1

Views: 2746

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

You misunderstand how a join works.

FROM tbl_profile JOIN tbl_users ON ...

joins the two tables, i.e. combines records on the given condition in ON.

FROM tbl_profile JOIN (SELECT * FROM tbl_users)

does exactly the same. It makes no difference if you join a table directly or join the records of the table, because this means exactly the same.

FROM tbl_profile JOIN (SELECT users_id, firstname, lastname FROM tbl_users)

again does the very same thing. Only that you restrict the columns you can use in the query to the three stated columns. So if there existed more columns in the table, you could not use them in the query's select or where or order by clause anymore.

So a join means just combining records. Which columns you want to show, you put in the select clause:

SELECT * FROM tbl_profile JOIN tbl_users ON ...

selects all columns from both tables.

SELECT p.department FROM tbl_profile p JOIN tbl_users u ON ...

selects only the department.

You want:

SELECT * FROM tbl_users u JOIN tbl_profile p ON p.profile_id = u.user_id

A LEFT JOIN by the way is an outer join where you keep the records from the left table in your results even when there is no match in the right table. In your query you said that you wanted to show profile records too that have no match in the users table, which was certainly not intended.

Upvotes: 2

Neetesh Kumar Gupta
Neetesh Kumar Gupta

Reputation: 126

As you want to list all the columns in both the tables with LEFT OUTER JOIN, the following query will serve your purpose:

SELECT * FROM users LEFT OUTER JOIN profile on users.users_id = profile.profile_id

You can use the alias as well if you want as following:

SELECT * FROM users u LEFT OUTER JOIN profile p on u.users_id = p.profile_id

Upvotes: 0

Nishant Nair
Nishant Nair

Reputation: 2007

You should use inner join not nested inner join 

    $sql = SELECT a.profile_id,a.department,a.specialization,b.users_id,b.firstname,
           b.lastname FROM tbl_profile AS a  inner join tbl_users b
           ON a.profile_id = b.users_id

Upvotes: 1

Related Questions