What is the left join that equals this righ join?

Question

Thanks first.

I have this right join worked fine, but when I try to change it into a left join I got an error.

The question is to get all information about the students from table Student who has higher score in class1 than class2 from a score-student table called SC.

Student(SId,Sname,Sage,Ssex)

SC(SId,CId,Score)

Here is my RIGHT JOIN:

SELECT * FROM Student RIGHT JOIN (
    SELECT t1.SId, class1, class2 FROM
      (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01')AS t1,
      (SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02')AS t2
    WHERE t1.SId = t2.SId
    AND t1.class1 > t2.class2
    )r 
ON Student.SId = r.SId;

And then I tried something like:

SELECT t1.SId, class1, class2, Student.* FROM
    (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01')AS t1,
    (SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02')AS t2
    WHERE t1.SId = t2.SId
    AND t1.class1 > t2.class2
)r
LEFT JOIN Student
ON Student.SId = r.SId;

but it didn`t work.

I am pretty new with SQL, wish you could explain a little bit.

I am using MySQL5.7

You got an error/it didn't work. *Details* of this error or failure would greatly enhance this question. If there was an error, the *actual error message*, if it didn't work in some other way - what *happened* versus what you expected? We don't have your data, we can't run these queries. — Damien_The_Unbeliever, Apr 27 '18 at 08:43
What didn't work is `SELECT t1.SId`. If you want an equal select you should keep it `SELECT * FROM` — Pham X. Bach, Apr 27 '18 at 08:44
Read this excellent article on sql joins; [MySQL Join Made Easy](http://www.mysqltutorial.org/mysql-join/) — cdaiga, Apr 27 '18 at 09:26
You should ask *another* question with sample data, desired results, and an explanation of what you are doing. There may be a simpler approach. — Gordon Linoff, Apr 27 '18 at 11:00
Your question is answered by reading definitions of left & right join. You have *some idea* because you tried to convert. Switching left & right arguments while switching "left" with "right" leaves a result the same. But you *didn't* swicth arguments--look closely at what you did. What would be helpful is if you explained what you thought you were accomplishing. Also, please read & act on [mcve]. PS Better code formatting would help. — philipxy, Apr 28 '18 at 02:47

score 3 · Answer 1 · answered Apr 27 '18 at 08:53

Generally, If you have a right join query:

SELECT select_statement
FROM table1 
RIGHT JOIN table2 
ON join_condition;

You just change it to left join query like this:

SELECT select_statement
FROM table2 
LEFT JOIN table1
ON join_condition;

For your case, when you changed your query to left join, you changed the select_statement, and it make error.

Just apply the above to your query:

SELECT * 
FROM  (
    SELECT t1.SId, class1, class2 
    FROM
        (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01') AS t1, 
        (SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02') AS t2
    WHERE t1.SId = t2.SId
        AND t1.class1 > t2.class2
) r 
LEFT JOIN Student
ON Student.SId = r.SId;

The "general" statement is true for two tables. It does not general to more than two tables. — Gordon Linoff, Apr 27 '18 at 11:00

score 0 · Accepted Answer · answered Apr 27 '18 at 09:23

The first query that works is here:

SELECT * FROM Student RIGHT JOIN (
    SELECT t1.SId, class1, class2 FROM
      (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01')AS t1, 
      (SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02')AS t2
      WHERE t1.SId = t2.SId
      AND t1.class1 > t2.class2
    )r 
ON Student.SId = r.SId;

The table r is being created via this sub-query:

SELECT t1.SId, class1, class2 FROM
  (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01')AS t1, 
  (SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02')AS t2
WHERE t1.SId = t2.SId
AND t1.class1 > t2.class2

t1.Sid, class1 and class2 are resolved without ambiguity. t1.Sid becomes available as r.Sid in the join Student.SId = r.Sid. Hence, the query works.

The second query that causes problem is here:

SELECT t1.SId, class1, class2, Student.* FROM
   (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01')AS t1, 
   (SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02')AS t2
  WHERE t1.SId = t2.SId
  AND t1.class1 > t2.class2
)r
LEFT JOIN Student
ON Student.SId = r.SId;

There are two issues to be addressed:

Issue 1: Student.* is not resolved since the FROM section does not have any reference to it.
Issue 2: Table r is not getting constructed correctly.

The two issues when addressed result in this modified second query, which works:

SELECT * from 
  (SELECT t1.SId, class1, class2 FROM
     (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01')AS t1,
     (SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02')AS t2
    WHERE t1.SId = t2.SId
    AND t1.class1 > t2.class2
  )r
LEFT JOIN Student
ON Student.SId = r.SId;

We must just find the arguments of the right join & swap them. You don't make that clear. — philipxy, Apr 28 '18 at 02:54

What is the left join that equals this righ join?

2 Answers2