A SQL query that will return only orphaned records

Posted on 2011-05-02
Medium Priority
Last Modified: 2012-05-11
Hello, I'm looking for a query that will return me table records that are orphaned.  Here is an example of what I'm looking for:

Table 1:  Authors table
author_name, etc.

Table 2:  Book table
book_name, etc

There is a one-to-one relationship between them.  Every author has a book_id that they are linked to.  I want a query that I can run on the book table that returns me all the books that have not been assigned to an author.

Question by:parlays
  • 2
  • 2

Author Comment

ID: 35508964
I was trying to run a query similar to this, I think it works but am not sure:
select book_id from book where (select book_id from author where author.book_id = book.book_id) IS NULL;

LVL 41

Expert Comment

ID: 35508970
try this.
select b.*
  from Book as b
  left join Authors as a on b.book_id = a.book_id
 where a.book_id is null

Open in new window

LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 35508979
or this.
select *
  from Book as b
 where not exists (select 1 from authors as a where b.book_id = a.book_id);

Open in new window


Author Closing Comment

ID: 35509002

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month16 days, 22 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question