?
Solved

A SQL query that will return only orphaned records

Posted on 2011-05-02
4
Medium Priority
?
207 Views
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_id
book_id
author_name, etc.

Table 2:  Book table
book_id
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.

Thanks!!
0
Comment
Question by:parlays
  • 2
  • 2
4 Comments
 

Author Comment

by:parlays
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;

0
 
LVL 41

Expert Comment

by:Sharath
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

0
 
LVL 41

Accepted Solution

by:
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

0
 

Author Closing Comment

by:parlays
ID: 35509002
Thanks!!
0

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