How to make a selection from 2 tables a boolean value?

Question

SQL

I have 2 tables linked by id. I need to take data from 1 table and a boolean value if data was found in the second

SELECT a.id, a.rank, a.firstname, a.name, 
a.secondname, boolean
FROM table1 as a
JOIN table2 as b on b.table1id = a.id and b.dateto < ?

Answer 1

The boolean type inference of a column must support the DBMS.
And so, in practice, it is guaranteed to work with null / not null or 0 / not 0 flags.
a) Use left join and check for a non-null value of some field of the attached table (for example, b.table1id).

SELECT distinct a.id, a.rank, a.firstname, a.name, 
a.secondname, b.table1id
FROM table1 as a
left JOIN table2 as b on b.table1id = a.id and b.dateto < ?

b) Or consider the number of rows as a subquery for a connected table. You can optimize by cutting off the first record rownum = 1, then cnt will get 0 or 1.

SELECT a.id, a.rank, a.firstname, a.name, 
a.secondname, (select count(*) from table2 as b on b.table1id = a.id and b.dateto < ? and rownum = 1) cnt
FROM table1 as a

c) If the logic in the column output is supported:

SELECT a.id, a.rank, a.firstname, a.name, 
a.secondname, exists(select 1 from table2 as b on b.table1id = a.id and b.dateto < ?) as is_exists
FROM table1 as a