How to prevent duplicate rows in sql cross join

I have books, authors and links table. I want to get all books that has in their author at least one from chosen. Links is table with connections between them: if you want to set some book author you need to create row in links with foerign keys book_id, author_id. What I’m trying to do:

SELECT * FROM books cross JOIN links
where user_id = 1 and
books.id = links.book_id and
links.author_id in (1, 2)

What i get

I want ot somehow get result without book duplicates, but I’m not sure that it is possible. What can I do only with sql commands in this case?

Answer

One method to avoid duplicate is to use exists with a correlated subquery rather than a join:

select b.* 
from books b
where exists (select 1 from links l where b.id = l.book_id and l.author_id in (1, 2))

This displays books that match, whithout their authors.

Another (probably less efficient) approach is aggregation and group_concat() to put all matching authors in the same row:

select b.id, b.title, b.year, b.price, group_concat(l.author_id) author_ids
from books b
inner join links l on l.book_id = b.id
where l.author_id in (1, 2)
group by b.id, b.title, b.year, b.price