How to get right join with a condition?-CodePudding

I have two tables:

appointment
citizen_id | date       | pregnant_consult
         1 | 2021-10-01 |                1
         2 | 2021-10-01 |                1
         3 | 2021-10-01 |                1
         4 | 2021-10-01 |                0
         5 | 2021-10-01 |                0

citizen
id | name   | pregnant
 1 | mary   |        1
 2 | jane   |        0
 3 | linda  |        0
 4 | susy   |        0
 5 | sandra |        1

I want to list all appointment with pregnant_consult = 1 join citizen

select * from appointment left join citizen on appointment.citizen_id = citizen.id where pregnant_consult = 1

this works ok:

citizen_id | date       | pregnant_consult | id | name  | pregnant
         1 | 2021-10-01 |                1 |  1 | mary  |        1
         2 | 2021-10-01 |                1 |  2 | jane  |        0
         3 | 2021-10-01 |                1 |  3 | linda |        0

but I need to listen the citizen SANDRA because she is pregnant = 1 like this:

citizen_id | date       | pregnant_consult | id | name  | pregnant
         1 | 2021-10-01 |                1 |  1 | mary  |        1
         2 | 2021-10-01 |                1 |  2 | jane  |        0
         3 | 2021-10-01 |                1 |  3 | linda |        0
      null |       null |             null |  5 | sandra|        1

Any idea to add "sandra" and not add "suzy"?

CodePudding user response：

There are lots of ways to approach. Thus it is hard to answer your question as you did not specify your requirements, limits, etc.

Try this:

(select * from appointment a right join citizen c on a.citizen_id = c.id where pregnant_consult = 1) union (select * from appointment a right join citizen c on a.citizen_id = c.id where pregnant = 1);

CodePudding user response：

The most simple solution to this question is to use OR operator as below

select * from appointment left join citizen on appointment.citizen_id = citizen.id where pregnant_consult = 1 or pregnant = 1

CodePudding user response：

There is no appointment without a citizen, isn't it? Why not swap the tables in the join like this?

select * from citizen left join appointment
on appointment.citizen_id = citizen.id 
where pregnant_consult = 1 or pregnant = 1

or the same using right join:

select * from appointment right join citizen 
on appointment.citizen_id = citizen.id 
where pregnant_consult = 1 or pregnant = 1