For the example table acts below:
| id | request_id |
|---|---|
| 1 | 234 |
| 2 | 531 |
and the query below:
select request_id, id
from acts
where id in (234,531,876)
I need to get the following result:
| request_id | id |
|---|---|
| 234 | 1 |
| 531 | 2 |
| 876 | null |
As you could see there is not row where request_id 876 exists. For these cases null should be returned.
How could I achieve this?
3
Assuming you actually meant to join on request_id not id.
create table acts (id integer, request_id integer);
insert into acts values (1, 234), (2, 531);
select
*
from
(values(234), (531), (876)) as t(r_id)
left join
acts on t.r_id = acts.request_id;
r_id | id | request_id
------+------+------------
234 | 1 | 234
531 | 2 | 531
876 | NULL | NULL
The above creates a VALUES list that you can then LEFT JOIN to acts. In that case the request_id missing in acts will show up with NULL values for the corresponding field in acts.
Conditional logic can be helpful in this situation.
for (Object item : database) {
if (item.getId() == id) {
return item;
}
}
return null;