There is a table

books id name date_created 1 book1 1469527903 2 book2 1469527903 3 book3 1469527903

There is a table

  sold_books book_id book_price 1 10 1 20 2 30 2 21

Here is the task: Deduce the number of books, if only the book_id in sold_books matches the book.id, and book.date_created = '1469527903'; In general, it should work: 2 Since the books with ID: 1, 2 are sold.

I try such a request

 SELECT count(books.id) count_sold_books FROM `books` as books INNER JOIN `sold_books` ON books.id = sold_books.book_id;

And I write 4 entries, then e of sold_books.

Tried both LEFT JOIN and RIGHT JOIN ...

    1 answer 1

    The easiest way is:

     SELECT count(DISTINCT books.id) count_sold_books FROM `books` as books INNER JOIN `sold_books` ON books.id = sold_books.book_id;

    But it will not work everywhere (in Access'e, for example, it will not work), then you can just wrap up in another request.

     SELECT COUNT(b.id) AS count_sold_books FROM( SELECT DISTINCT books.id FROM `books` as books INNER JOIN `sold_books` ON books.id = sold_books.book_id; ) AS b

    look here

    • And if I still need to add a condition, WHERE b.date_created = '1469527903', how to add? I finish after 'AS b', but it says to me that there is no such column, although it is there .. A complicated query for me is user190134
    • Everything, it turned out, it was necessary to add a condition before closing the parentheses. - user190134