-1

I have got two tables:

create table student
(
    studentid       bigint primary key not null,
    name            varchar(200)        not null
);

create table courseregistration
(
    studentid       bigint not null,
    coursenamename  varchar(200) not null,
    isfinished        boolean default false
); 

--insert some data
insert into student values(1,'Dave');
insert into courseregistration values(1,'SQL',true);

Student is fetched with id, so it should be always returned in the result. Entry in the courseregistration is optional and should be returned if there are matching rows and those matching rows should be filtered on isfinished=false. This means I want to get the course regsitrations that are not finished yet. Tried to outer join student with courseregistration and filter courseregistration on isfinished=false. Note that, I still want to retrieve the student.

Trying this returns no rows:

select * from student
left outer join courseregistration using(studentid)
where studentid = 1
and courseregistration.isfinished = false

What I'd want in the example above, is a result set with 1 row student, but course rows null (because the only example has the isfinished=true). One more constraint though. If there is no corresponding row in courseregistration, there should still be a result for the student entry.

This is an adjusted example. I can tweak my code to solve the problem, but I really wonder, what is the "correct/smart way" of solving this in postgresql?

PS I have used the (+) in Oracle previously to solve similar issues.

naimdjon
  • 3,162
  • 1
  • 20
  • 41
  • LEFT JOIN returns INNER JOIN rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After a LEFT JOIN, a WHERE, HAVING or INNER JOIN that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS This is a faq. But you have to write many clear, concise & precise phrasings of your question/problem/goal to be able to search. – philipxy Apr 09 '21 at 22:10
  • You don't clearly state what rows go in the result as a function of input. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation or input tables in terms of its column values. So: I have a row (studentid, ...); under what condition does it go in the result? – philipxy Apr 09 '21 at 22:10
  • Likely you could use LEFT JOIN USING with a RHS subquery of finished courses. But that's the same as a LEFT JOIN with RHS courses ON ids = & isfinished. If you only want rows where student=1, you can use a LHS student subquery in the USING case or add a final WHERE to either case. – philipxy Apr 09 '21 at 22:26
  • Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy Apr 09 '21 at 22:26
  • The link I gave is a duplicate of your problem (which is a duplicate many many times over) & the answer you accepted fixes it by moving a test from the where into the on per the duplicate answer. (And that answer is one of the options I described in my other comment.) – philipxy Apr 10 '21 at 19:45
  • The difference is that the other SO question uses group by, a multiple functions (e.g. DAY()) within the query and it is for mySQL. I still think I specified in the question what rows I want in the result, i.e. the student is always returned in the result but the courseregistration is optional. The accepted answer is what I was looking for. – naimdjon Apr 11 '21 at 08:37
  • @philipxy updated the question to make it more clear. And BTW, you had put a really good explanation in your comments, it definitely deserves to be put in the answer. – naimdjon Apr 11 '21 at 08:42
  • Re answering, thank you, but duplicates should be closed not answered, and there are a zillion other Q&A about this, and I said how you can find them in my first comment. Re clearly saying what function of inputs you wanted, you just give a bunch of unclear fragmented properties. Good luck. – philipxy Apr 11 '21 at 09:23

1 Answers1

1

Isn't this what you are looking for :

select * from student s
left outer join courseregistration  cr
   on s.studentid = cr.studentid
   and cr.isfinished = false
where s.studentid = 1

db<>fiddle here

eshirvana
  • 23,227
  • 3
  • 22
  • 38