Q
Problem solve Get help with specific problems with your technologies, process and projects.

Finding users who are not in the BOAT table

I'm a newby who's having trouble with what seems a very simple query. Basically I have these two tables with data as shown:

Table BOAT:
----------------
ID    USER_ID
10    4
11    5

Table BOAT_USERS
-----------------------
USER_ID    NAME
1                Christina
2                James
3                Daisy
4                William
5                Mike
And all I want to do is a query which will give me all the USER_IDs and NAMEs of the people who are NOT in the BOAT table. In other words, a query which could return ONLY the USER_IDs 1, 2, and 3, and NOT 4 and 5 which are already in the BOAT table. I tried the following query and several variations but it didn't work:
FROM BOAT_USERS U, BOAT B
WHERE B.USER_ID != U.USER_ID
So can you help me with that? 

You bet I can help! Here's the query you'll need:

SELECT *
  FROM boat_users
 WHERE user_id NOT IN (SELECT user_id FROM boat) ;
There are actually several different ways that you could write this query, but I figured this one would be relatively easy to understand.

First, note the sub-query (in parentheses in the WHERE clause). This query will run first and substitute the values it returns into the where clause of the outer query. The effect is something like this:

SELECT *
  FROM boat_users
 WHERE user_id NOT IN (4, 5) ; <-- after the sub-query runs and substitutes
it's result
So that you get the result you want which is:
   USER_ID NAME
---------- -----------------
         1 Christina
         2 James
         3 Daisy
Hope that helps!

For More Information


Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close