CODEWITHSUNDEEP
mysqlsql
kyun
kyun

Reputation: 10264

how to join SQL with 3 tables

enter image description here

I'm trying to study SQL.

I have a problem with JOIN

I want to display ref_id, pro_name, class_name but I couldn't.

I find EFFICIENT solution.

MY QUERY (DOESN'T WORK)

SELECT
  ref_id, pro_name, class_name
FROM
  RC, RP, PP, LP
WHERE
  RC.ref_id = RP.ref_id

Upvotes: 2

Views: 64

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

You would seem to want:

select rp.pro_id, pp.pro_name, lp.class_name
from rp left join
     pp
     on rp.pro_id = pp.pro_id left join
     lp
     on rp.lec_id = lp.lec_id;

Note the use of left join. This ensure that all rows are in the result set, even when one or the other joins doesn't find a matching record.

From what I can see, the table rc is not needed to answer this specific question.

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

Avoid using commas be CROSS JOIN

You could use JOIN to instead of commas

like this.

SELECT
  RP.ref_id, PP.pro_name, LP.class_name
FROM
  RP
LEFT JOIN RC ON RC.ref_id = RP.ref_id
LEFT JOIN PP ON PP.pro_id = RP.pro_id
LEFT JOIN LP ON LP.lec_id = RP.lec_id

Upvotes: 1

Related Questions