FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
Home / Tricky Select Queries / Question No: 92

How to find the books which have authors with more than one book in the table?

I have a child table named author_books where I store books against each author. So, I want a query to find the books which have authors with more than one book in this table. The structure of the table is as below:
CREATE TABLE `author_books` (
`ab_id` INT NOT NULL ,
`ab_author_id` MEDIUMINT NOT NULL ,
`ab_book_id` BIGINT NOT NULL

Answer No: 92

This is can be done through creating drive table of author_books table and then joining the both. First part of the query will find the authors with multiple books. Then join the drived table to that on ab_author_id. Take the example below:
SELECT ab_1.ab_author_id, ab_1.ab_book_id
FROM author_books ab_1

SELECT ab_author_id, COUNT( ab_book_id )
FROM author_books ab_2
GROUP BY ab_author_id
HAVING COUNT( ab_book_id ) >1

) AS ab_3 ON ab_1.ab_author_id = ab_3.ab_author_id;

Related MySQL FAQs to the Above FAQ

How-to-do-numeric-sorting-within-a-string-field-in-MySQL How to do numeric sorting within a string field in MySQL?

How-to-take-data-back-up-of-single-table How to take data back up of single table?

How-to-find-duplicate-records-from-a-table-in-MySQL How to find duplicate records from a table in MySQL?

How-to-get-last-top-records-in-asceding-order How to get last top records in asceding order?

How-to-move-table-from-one-to-another-MySQL-database How to move table from one to another MySQL database?

How-to-get-parent-table-child-table-and-grandchild-table-aggregates-in-MySQL How to get parent table child table and grandchild table aggregates in MySQL?

How-to-skip-repeating-values-from-the-query-results How to skip repeating values from the query results?

How-to-retrieve--one-child-row-against-each-parent-id How to retrieve one child row against each parent id?

An-important-trick-to-speed-up-select-query An important trick to speed up select query.

How-to-count-the-number-of-rows-containing-APOSTROPHE-in-MySQL How to count the number of rows containing APOSTROPHE in MySQL?

How-to-compare-data-of-two-tables-of-two-different-databases-in-MySQL How to compare data of two tables of two different databases in MySQL?

How-to-retreive-multiple-records-without-a-reference-table How to retreive multiple records without a reference table?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: | | | |

© 2021
All rights reserved.