How to Join multiple tables?

Question

I tried this query for two tables and it worked, but if I want to do it for many tables how it is done?

cmd.CommandText = "SELECT *  FROM assignments 
                   inner join Customers on assignments.Customer_ID = customers.Customer_ID";
//assignments and customers are tables

Provide DDL with some sample data and expected result out of the sample data. — Abhik Chakraborty, Apr 16 '15 at 10:14
Are you asking for help w.r.t SQL Query ? If yes, check my answer. If not, kindly clarify on DDL & sample data & expected output. — Alekhya Vemavarapu, Apr 16 '15 at 10:18

score 1 · Accepted Answer · edited Apr 16 '15 at 11:44

1

Consider agents,customer,orders to be your tables & you gotta join them.

SELECT 
    a.ord_num,
    b.cust_name,
    a.cust_code,  
    c.agent_code,
    b.cust_city  
FROM agents c, customer b, orders a  
WHERE b.cust_city = c.working_area  
    AND a.cust_code = b.cust_code  
    AND a.agent_code = c.agent_code;

Regards!

edited Apr 16 '15 at 11:44

veljasije

6,722
12
48
79

answered Apr 16 '15 at 10:16

Alekhya Vemavarapu

1,145
1
10
26

what is a , b, c ? is the name of tables ? – ronaldo cr7 Apr 16 '15 at 10:19
Line 3: a is orders, b is customer, c is agents. This is called Alias-ing. – Alekhya Vemavarapu Apr 16 '15 at 10:20
You can also do Alias-ing using keyword "as": "FROM agents as c,customer as b,orders as a " – Alekhya Vemavarapu Apr 16 '15 at 10:22
Accept the answer if you understood it. So that it'll be helpful to others. Regards. – Alekhya Vemavarapu Apr 16 '15 at 10:23
I gotta love that you aliased orders to a, agents to c and customers to b :) Mnemonic disaster. {edit} I tend to alias tables all the time (for space and readability) and I alias them by the initials of individual words (to remember who's who). – Liz Apr 16 '15 at 10:26
Memory tuner :P Thanks for that :D – Alekhya Vemavarapu Apr 16 '15 at 10:29

score 1 · Answer 2 · edited Apr 16 '15 at 11:42

1

Here I am giving one example. You can create queries like this one:

select 
    * 
from tblA a 
     inner join tblB b 
         on a.id = b.id 
     inner join tblC 
         on a.id = c.id 
     inner join tblD 
         on a.id = d.id

edited Apr 16 '15 at 11:42

veljasije

6,722
12
48
79

answered Apr 16 '15 at 10:25

sheshadri

1,207
9
21

score 0 · Answer 3 · answered Apr 16 '15 at 10:23

0

If you are using SQL management studio, right click and select "Design Query in Editor". This is the easiest way to join your tables (it's visual)

answered Apr 16 '15 at 10:23

z0mbi3

336
4
14

Good solution when someone is trying to do querying from the workbench ! – Alekhya Vemavarapu Apr 16 '15 at 10:24
SQL Management Studio is graphical tool for work with MSSQL Server, not with MySQL. – veljasije Apr 16 '15 at 11:43

score 0 · Answer 4 · answered Apr 17 '15 at 06:00

0

May be this will help you

SELECT * FROM assignments AS a, customers AS c WHERE a.Customer_ID = c.Customer_ID;

answered Apr 17 '15 at 06:00

Sarang

754
3
9
24

How to Join multiple tables?

4 Answers4