Joining two tables with specific columns

Question

I am new to SQL, I know this is really basic but I really do not know how to do it! I am joining two tables, each tables lets say has 5 columns, joining them will give me 10 columns in total which I really do not want. What I want is to select specific columns from both of the tables so that they only show after the join. (I want to reduce my joining result to specific columns only)

SELECT * FROM tbEmployees

JOIN tbSupervisor

ON tbEmployees.ID = tbSupervisor.SupervisorID

The syntax above will give me all columns which I don't want. I just want EmpName, Address from the tblEmployees table and Name, Address, project from the tbSupervisor table

I know this step:

SELECT EmpName, Address FROM tbEmployees

JOIN tbSupervisor

ON tbEmployees.ID = tbSupervisor.SupervisorID

but I am not sure about the supervisor table.

I am using SQL Server.

You just need to type out the column names that you need from each table. — Taryn, Jul 02 '13 at 20:13

score 45 · Accepted Answer · edited Feb 24 '20 at 14:49

45

This is what you need:

Select e.EmpName, e.Address, s.Name, S.Address, s.Project
From tbEmployees e
JOIN tbSupervisor s on e.id = SupervisorID

You can read about this on W3Schools for more info.

edited Feb 24 '20 at 14:49

Nicolas Gervais

33,817
13
115
143

answered Jul 02 '13 at 20:14

OCDan

1,103
1
10
19

Minute correction for MS SQL... `Select e.EmpName, e.Address, s.Name, S.Address, s.Project From tbEmployees e JOIN tbSupervisor s on e.id = s.SupervisorID` – Manoj Kumar Aug 20 '16 at 14:47

score 16 · Answer 2 · answered Jul 02 '13 at 20:12

16

You can get columns from specific tables, either by their full name or using an alias:

SELECT E.EmpName, E.Address, S.Name, S.Address, S.Project
FROM tbEmployees E
INNER JOIN tbSupervisor S ON E.ID = S.SupervisorID

answered Jul 02 '13 at 20:12

Fenton

241,084
71
387
401

score 6 · Answer 3 · answered Jul 02 '13 at 20:14

You can use the table name as part of the column specification:

SELECT tbEmployees.EmpName, tbEmployeesAddress, tbSupervisor.Name,
       tbSupervisor.Address, tbSupervisor.project

FROM tbEmployees

JOIN tbSupervisor

ON tbEmployees.ID = tbSupervisor.SupervisorID

score 5 · Answer 4 · answered Jul 02 '13 at 20:26



    SELECT employees.EmpName, employees.Address AS employeer address, 
           supervisor.Name, supervisor.Address AS supervisor address,supervisor.project 
    FROM tbEmployees 
       AS employees 
    JOIN tbSupervisor 
       AS supervisor 
    ON 
       employees.ID = supervisor.SupervisorID

score 2 · Answer 5 · edited Jan 06 '20 at 05:30

2

SELECT product_report.*, 
       product.pgroup 
FROM   `product_report` 
       INNER JOIN product 
               ON product_report.product_id = product.id 
WHERE  product.pgroup = '5' 
ORDER  BY product.id DESC

edited Jan 06 '20 at 05:30

4b0

21,981
30
95
142

answered Jan 06 '20 at 05:28

Md Yeasin Arafat

2,683
1
10
5

1

It looks like your post is mostly code, please add some more details – Dinesh Ghule Jan 06 '20 at 07:15

score 1 · Answer 6 · answered Jul 02 '13 at 20:14

You need to learn about aliases. They will make your queries more maintainable. Also, you should always use aliases when referencing columns, so your query is clear about what it is doing:

SELECT e.EmpName, e.Address, s.name, s.address as SupervisorAddress
FROM tbEmployees e  JOIN
     tbSupervisor s
     ON e.ID = s.SupervisorID;

Note that I also renamed the second address so its name is unique.

score 1 · Answer 7 · edited Jun 07 '18 at 07:11

1

Specify the table name and field name in your selection

SELECT tbEmployees.EmpName,
       tbEmployees.Address,
       tbSupervisor.[column name]
  FROM tbEmployees
  JOIN tbSupervisor ON tbEmployees.ID = tbSupervisor.SupervisorID

edited Jun 07 '18 at 07:11

Sudipta Mondal

2,550
1
19
20

answered Jul 02 '13 at 20:14

Riv

1,849
1
16
16

Joining two tables with specific columns

7 Answers7