Number of entries in one table if the records match the other table



  • A table

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

    A table

     sold_books 
        book_id book_price
        1       10
        1       20
        2       30
        2       21
    

    That's challenge: Remove the ring from the books, unless book_id on sold_books matches book.id, and book.date_created = '1469527903'; Well, it's gonna work: 2 Because books from the idy: 1, 2 sold.

    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 get kicked out. 4 I don't know what you're talking about.

    I tried LEFT JOIN and RIGHT JOIN. ♪ ♪



  • Just like this:

    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's not gonna work everywhere.

    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
    

    http://www.w3schools.com/sql/sql_func_count.asp


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2