Join three tables with CROSS JOIN

Question

I have three tables:

student:        sub:           marks:

id | name     id | sub         stud_id | sub_id | marks
---+--------  ---+------------  --------+----------+------
 1 | Peter     1 | English            1 |        1 |    80
 2 | Alice     2 | Maths              1 |        2 |    70
               3 | History            2 |        1 |    90
                                      2 |        2 |    80

I need to make a query that returns this:

 name  | subjects  | marks
-------+-----------+----------------------------
  Peter | English  | 80
  Peter | Maths    | 70
  Peter | History  | 0

This is what I tried:

select * 
from student s 
LEFT JOIN stud_marks m ON s.id = m.stud_id 
RIGHT JOIN subjects sub ON sub.id = m.sub_id

which got me

name  | marks  | sub
------+--------+----------
Peter | 80     | English  
Peter | 70     | Maths    
Alice | 90     | English  
Alice | 80     | Maths    
      |        | History

Any ideas?

What do "I tried" & "get weird results" mean? How is that desired output a function of what input? "Any ideas" is not a question suitable for SO. Please read & act on [ask] and [mcve]. Also: Always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. PS Your title suggests you might need separate aggregations joined together. — philipxy, Jun 21 '18 at 16:08
I tried with above tables to get output with below one. what I have tried with SQL QUERY(select * from student s LEFT JOIN stud_marks m ON s.id = m.stud_id RIGHT JOIN subjects sub ON sub.id = m.sub_id) . But I got some unwanted results. — Vinil Lakkavatri, Jun 22 '18 at 06:34
As you say in the title: you need a cross join and there is none in your query. Then I advise never to use right outer joins. They are much harder to read than left outer joins. I understand the idea behind your query, but what it does is get all students with their marks, and then you say you want all subjects, even if there is no student with marks in that subject, and this is what you get; an additional empty History row. — Thorsten Kettner, Jun 22 '18 at 06:48
I've added your query and result to the request. Next time you should do this yourself, please. — Thorsten Kettner, Jun 22 '18 at 06:53
Please clarify by post edits, not comments. (But--"unwanted" tells us nothing. And you still haven't explained "wanted".) — philipxy, Jun 23 '18 at 21:37

score 0 · Answer 1 · answered Jun 22 '18 at 06:36

You'd cross join Peter with all subs. Then outer join the marks:

select
  peter.name,
  sub.sub,
  coalesce(marks.marks, 0) as marks
from (select * from student where id = 1) peter
cross join sub
left join marks on marks.stud_id = peter.id and marks.sub_id = sub.id
order by sub.sub;

(It's not really necessary to select Peter in a subquery, but I think it makes it very clear what we are doing here. We could just as well cross join students with marks and limit this to Peter in the WHERE clause.)

Join three tables with CROSS JOIN

1 Answers1