Joining three tables using MySQL

639

I have three tables named

**Student Table**

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

{-code-2}

I build following query

{-code-3}

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:

{-code-4}

And wants to get this result:

{-code-5}
65

Answer

--- id name
83

Answer

--- 1 ali 2 ahmed 3 john 4 king **Course Table**
829

Answer

--- id name
594

Answer

--- 1 physic 2 maths 3 computer 4 chemistry **Bridge**
52

Answer

--- sid cid
532

Answer

--- 1 1 1 2 1 3 1 4 2 1 2 2 3 3 3 4 4 1 4 2|||**Result**
644

Answer

------- Student Course
67

Answer

------- ahmed physic ahmed maths ahmed computer ahmed chemistry ali physic ali maths john computer john chemistry king physic king maths|||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|||**employee**
155

Answer

--------- id name
881

Answer

--------- 1 ali 2 king 3 mak 4 sam 5 jon **manage**
922

Answer

---- mid eid
667

Answer

---- 1 2 1 3 3 4 4 5|||**result**
976

Answer

Manager Staff
109

Answer

ali king ali mak mak sam sam jon
810

Answer

Solution:

Use ANSI syntax and it will be a lot more clear how you are joining the tables:

SELECT s.name as Student, c.name as Course 
FROM student s
    INNER JOIN bridge b ON s.id = b.sid
    INNER JOIN course c ON b.cid  = c.id 
ORDER BY s.name 
136

Answer

Solution:

Simply use:

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

Answer

Solution:

For normalize form

select e1.name as 'Manager', e2.name as 'Staff'
from employee e1 
left join manage m on m.mid = e1.id
left join employee e2 on m.eid = e2.id
820

Answer

Solution:

SELECT *
FROM user u
JOIN user_clockits uc ON u.user_id=uc.user_id
JOIN clockits cl ON cl.clockits_id=uc.clockits_id
WHERE user_id = 158
245

Answer

Solution:

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`
544

Answer

Solution:

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 fetch100x100x100 = 1000000 times. It had to fetch for1 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!!!

547

Answer

Solution:

SELECT 
employees.id, 
CONCAT(employees.f_name," ",employees.l_name) AS   'Full Name', genders.gender_name AS 'Sex', 
depts.dept_name AS 'Team Name', 
pay_grades.pay_grade_name AS 'Band', 
designations.designation_name AS 'Role' 
FROM employees 
LEFT JOIN genders ON employees.gender_id = genders.id 
LEFT JOIN depts ON employees.dept_id = depts.id 
LEFT JOIN pay_grades ON employees.pay_grade_id = pay_grades.id 
LEFT JOIN designations ON employees.designation_id = designations.id 
ORDER BY employees.id;

You can JOIN multiple TABLES like this example above.

239

Answer

Solution:

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).

743

Answer

Solution:

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
246

Answer

Solution:

Query to join more than two tables:

SELECT ops.field_id, ops.option_id, ops.label
FROM engine4_user_fields_maps AS map 
JOIN engine4_user_fields_meta AS meta ON map.`child_id` = meta.field_id
JOIN engine4_user_fields_options AS ops ON map.child_id = ops.field_id 
WHERE map.option_id =39 AND meta.type LIKE 'outcomeresult' LIMIT 0 , 30
552

Answer

Solution:

Use this:

SELECT s.name AS Student, c.name AS Course 
FROM student s 
  LEFT JOIN (bridge b CROSS JOIN course c) 
    ON (s.id = b.sid AND b.cid = c.id);

People are also looking for solutions to the problem: php - Pagination not working in Laravel

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.