How to Display a join of tables by condition ??

Question

SQL

Hello.
Prompt: There is a huge table, as a matter of fact the directory with one condition where and pieces 30 joinov.
3 CTEs are used.

It's hard to articulate but I'll try on books.

There is a table of books, there is a table of authors - they are merged into one catalog

If the Books catalog contains a book with code 1, and the Authors catalog has an Ancestor of the book (obtained via CTE) with codes 1 and 2, then output only one line with code 1.
AND IF in the catalog of books the Code of the Author of the book does not match None of the code of the Book in the catalog of the author, then write the word "EMPTY" or leave an empty cell.

I did the first option through inner join, but I don’t know how to do it by condition, if there is a mismatch so that the lines are EMPTY, I have never come across this.

Answer 1

left join

Answer 2

For the described example, left join is suitable, and to output a string if there is a mismatch, use the IsNull and Cast functions:
TSQL example
Result :

+==================+======+===========+
| name             | code | AutorCode |
+==================+======+===========+
| BookWithAutor    | 1    | 1         |
+------------------+------+-----------+
| BookWithoutAutor | 3    | Empty     |
+------------------+------+-----------+

Answer 3

A book without an author?
Code of the book in the list of authors?
If the first is still possible, if the record is lost, then the second is rather a relic of a manual account, which should not be tormented in the DBMS.
Maybe not so elegant, but it's clearer to do UNION
1. select ..., min(author_id) from books inner join authors on books.author_id = authors.author_id
group by ...
2. select ..., 'empty' as author_id from books where not exists ( select 1 from authors where author_id = books.author_id)
and represent them with
1 union 2
-- CTEs work fine with unions, they should be moved to the front of the expression.
-- If the authors have a parent-child relationship, then these relationships are tracked in the CTE, the level indicator is also set there, by which you can select the highest record in the selection ( where parent_level = 1 )