Join multiple columns from 3 tables

Question

Looking at this solution: MySql Join three tables

I see that I can join 3 tables by doing something like this:

select s.name "Student", c.name "Course"
from student s, bridge b, course c
where b.sid = s.sid and b.cid = c.cid

But it seems like I can only select a single column from each table? I need to join multiple colums from 3 tables with sys_visits being the main table where userID=contrID (sys_users, sys_visits) and clientID=clientID (sys_client, sys_visits)

SELECT pid, clientID, contrID, serviceDate, serviceStart, serviceStop FROM sys_visits
SELECT userGroupID, userID, fname, lname FROM sys_users
SELECT clientID, clientFName, clientLName FROM sys_client ORDER BY clientLName ASC

I've tried numerous variations and can't seem to get the syntax correct?

When you want to join you should use `joins` :) and Ofcourse you can select any number if columns. — bonCodigo, Dec 06 '12 at 03:10
doesn't mysql default to inner join when no join type is specified? But yes in general you can select any/all columns from any number of tables being queried. [edit] and you can base your join on the equivalence of any number of columns as well. — akronymn, Dec 06 '12 at 03:17

John Woo · Accepted Answer · 2012-12-06T03:22:34.640

4

use the ANSI SQL-92 format of join instead.

SELECT  a.*, b.*, c.*
FROM    sys_users a
        INNER JOIN sys_visits b
            ON a.userID = b.contrlD
        INNER JOIN sys_client c
            ON b.clientID = c.clientID

the use of asterisks in the query indicates that you are selecting all columns from the tables. If you want to only choose specific columns, declare the column names explicitly on the query, example:

SELECT  a.userGroupID, a.userID, a.fname, a.lname,
        b.*, 
        c.*
FROM    sys_users a
        INNER JOIN sys_visits b
            ON a.userID = b.contrlD
        INNER JOIN sys_client c
            ON b.clientID = c.clientID

edited Dec 06 '12 at 03:22

answered Dec 06 '12 at 03:13

John Woo

258,903
69
498
492

I used this one,but in my condition,I have 2 columns with only 1 row filled and one column in the 3rd table with 2 rows filled.So when I fetch,all of this,iam getting only 1 row from all tables!! – Amal lal T L Jan 01 '18 at 09:06

Muthu Kumaran · Answer 2 · 2012-12-06T03:18:21.560

1

You can select any number columns from the tables.

    SELECT sv.pid, sv.clientID, sv.contrID, sv.serviceDate, sv.serviceStart, sv.serviceStop, su.userGroupID, su.userID, su.fname, su.lname, sc.clientFName, sc.clientLName 
    FROM sys_visits sv, sys_users su, sys_client sc 
    ORDER BY sc.clientLName ASC

Heres another method using JOINS [Recommended]

SELECT sv.pid, sv.clientID, sv.contrID, sv.serviceDate, sv.serviceStart, sv.serviceStop, su.userGroupID, su.userID, su.fname, su.lname, sc.clientFName, sc.clientLName FROM sys_visits sv
JOIN sys_users su ON su.userID = sv.contrID 
JOIN sys_client sc ON sc.clientID = sv.clientID 
ORDER BY sc.clientLName ASC

edited Dec 06 '12 at 03:18

answered Dec 06 '12 at 03:12

Muthu Kumaran

17,682
5
47
70

1

the second query is fine but the first one produces `CROSS JOIN`. – John Woo Dec 06 '12 at 03:16
Yes, you are right. I'm trying to explain OP multiple table column can be selected on the first query. – Muthu Kumaran Dec 06 '12 at 03:20

score 1 · Answer 3 · answered Mar 02 '18 at 18:15

Think if you have three tables for example

designation,qualification,specialization and teacher table.Here if you want to join three tables inside teacher table that has teacher_id, teacher_name, designation_id, qualification_id , specialization_id ,status etc...

    SELECT 
    t.name as teacher_name,
    d.name as designation_name,
    q.name as qualification_name,
    s.name as specialization_name
       FROM teachers t
    INNER JOIN designation d ON t.designation_id = d.id
    INNER JOIN qualification q ON t.qualification_id = q.id
    INNER JOIN specialization s ON t.specialization_id = s.id
    WHERE t.status = 1;

Join multiple columns from 3 tables

3 Answers3