Joining three tables using MySQL

user_233679 0
Viewed 402531

I have three tables named

**Student Table**
-------------
id    name
-------------
1     ali
2     ahmed
3     john
4     king

**Course Table**
-------------
id    name
-------------
1     physic
2     maths
3     computer
4     chemistry

**Bridge**
-------------
sid    cid
-------------
1     1
1     2
1     3
1     4
2     1
2     2
3     3
3     4
4     1
4     2

Now to show the student name with the course name which he had studied like,

**Result**
---------------------------
Student        Course
---------------------------
ahmed         physic
ahmed         maths
ahmed         computer
ahmed         chemistry
ali           physic
ali           maths
john          computer
john          chemistry
king          physic
king          maths

I build following query

select s.name as Student, c.name as Course from student s, course c join bridge b on c.id = b.cid order by s.name

But it does not return the required result...

And what would be for normalized form, if I want to find who is manager over other:

**employee**
-------------------
id        name
-------------------
1         ali
2         king
3         mak
4         sam
5         jon

**manage**
--------------
mid      eid
--------------
1         2
1         3
3         4
4         5

And wants to get this result:

**result**
--------------------
Manager      Staff
--------------------
ali          king
ali          mak
mak          sam
sam          jon
mysql
11 Answers

Don't join like that. It's a really really bad practice!!! It will slow down the performance in fetching with massive data. For example, if there were 100 rows in each tables, database server have to fetch 100x100x100 = 1000000 times. It had to fetch for 1 million times. To overcome that problem, join the first two table that can fetch result in minimum possible matching(It's up to your database schema). Use that result in Subquery and then join it with the third table and fetch it. For the very first join --> 100x100= 10000 times and suppose we get 5 matching result. And then we join the third table with the result --> 5x100 = 500. Total fetch = 10000+500 = 10500 times only. And thus, the performance went up!!!

user_91093720

join query with three tables and we want two values from the same column we set the alias name for every table in the joins. Same table name also declare as a different names.

const sql = `select p.ID,p.purchaseamount,urs.name as 
            buyername,pd.productname,
            pd.amount,urs1.name as sellername
            from  purchases p
            left join products pd on p.productid=pd.ID
            left join users urs on p.userid=urs.ID 
            left join users urs1 on  pd.userid=urs1.ID`
user_115076040

Just adding a point to previous answers that in MySQL we can either use

table_factor syntax

OR

joined_table syntax

mysql documentation

Table_factor example

SELECT prd.name, b.name 
FROM products prd, buyers b

Joined Table example

SELECT prd.name, b.name 
FROM products prd
 left join buyers b on b.bid = prd.bid;

FYI: Please ignore the fact the the left join on the joined table example doesnot make much sense (in reality we would use some sort of join table to link buyer to the product table instead of saving buyerID in product table).

user_34887470 
Query for three table join and limit set

SELECT * FROM (SELECT t1.follower_userid, t2.*, t3.login_thumb, t3.login_name, 
  t3.bio, t3.account_status, t3.gender
     FROM videos t2
      LEFT JOIN follower t1
        ON t1.follower_userid = t2.user_id 
         LEFT JOIN videos_user t3 
          ON t1.follower_userid  = t3.login_userid
           WHERE t1.following_userid='$userid'
            LIMIT $startpoint , $limit) AS ID 
             ORDER BY ID DESC
user_68701860
Related