CODEWITHSUNDEEP
mysqlsql
Dmitriy
Dmitriy

Reputation: 850

Don't know how to merge two MySQL tables

I'm new in SQL queries. I have a problem with the query.

I have table books:

+----+-------+--------+-----------+
| id | title | author | publisher |
+----+-------+--------+-----------+
|  1 | Book1 |      1 |         1 |
|  2 | Book2 |      1 |         2 |
|  3 | Book3 |      2 |         1 |
|  4 | Book4 |      2 |         2 |
|  5 | Book5 |      2 |         3 |
+----+-------+--------+-----------+

And I'm have another table, which contains copies of books with given book_id.

+----+---------+
| id | book_id |
+----+---------+
|  1 |       1 |
|  2 |       1 |
|  3 |       1 |
|  4 |       2 |
|  5 |       2 |
|  6 |       3 |
|  7 |       4 |
|  8 |       4 |
|  9 |       5 |
+----+---------+

All I need is to merge these two tables into one to have table sorted by amount of book copies.

I found solution to get table sorted by amount of book copies:

select book_copies.book_id, count(*) total_count 
from book_copies 
group by book_id 
having count(*) > 0 
order by count(*) desc;

+---------+-------------+
| book_id | total_count |
+---------+-------------+
|       1 |           3 |
|       2 |           2 |
|       4 |           2 |
|       3 |           1 |
|       5 |           1 |
+---------+-------------+

Now I don't know how to merge them.

I've tried like this: 

select books.title from books 
left join 
(select book_copies.book_id, count(*) total_count 
from book_copies 
group by book_id 
having count(*) > 0 
order by count(*) desc) 
as total_table on books.id = total_table.book_id;

But all I get was this: 

+-------+
| title |
+-------+
| Book1 |
| Book2 |
| Book3 |
| Book4 |
| Book5 |
+-------+

Could you help me please?

EDIT: by merging I meant smth like this:

+-------+--------+-----------+-----+
| title | author | publisher | tot |
+-------+--------+-----------+-----+
| Book1 |      1 |         1 |   3 |
| Book2 |      1 |         2 |   2 |
| Book4 |      2 |         2 |   2 |
| Book3 |      2 |         1 |   1 |
+-------+--------+-----------+-----+

Many thanks to @Marco for the answer!

Dmitriy

Upvotes: 2

Views: 121

Answers (1)

Marco
Marco

Reputation: 57573

I think you could try:

SELECT b.title, b.author, b.publisher, COUNT(bc.book_id) AS tot
FROM books b LEFT JOIN  book_copies bc
    ON b.id = bc.book_id
GROUP BY b.id

EDITED:
If you want sort, you can try

SELECT * FROM
  (SELECT b.title, b.author, b.publisher, COUNT(bc.book_id) AS tot
    FROM books b LEFT JOIN  book_copies bc
        ON b.id = bc.book_id
    GROUP BY b.id) g
ORDER BY g.tot DESC

Upvotes: 2

Related Questions