OraFAQ Forum: SQL & PL/SQL » Can you help with this simple SQL query?

Home » SQL & PL/SQL » SQL & PL/SQL » Can you help with this simple SQL query?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Can you help with this simple SQL query? [message #21653]

Wed, 21 August 2002 13:36

Noches Bellas
Messages: 1
Registered: August 2002

Junior Member

Hi, 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 din't work:
FROM BOAT_USERS U, BOAT B
WHERE B.USER_ID != U.USER_ID

So can you help me with that?

Noches Bellas

Report message to a moderator

Re: Can you help with this simple SQL query? [message #21654 is a reply to message #21653]

Wed, 21 August 2002 13:48

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Here are a couple of ways:

select *
  from boat_users
 where user_id not in (select user_id
                         from boat);
 
select *
  from boat_users bu
 where not exists
   (select null
      from boat b
     where b.user_id = bu.user_id);

Report message to a moderator

Re: Can you help with this simple SQL query? [message #21672 is a reply to message #21653]

Thu, 22 August 2002 01:38

V Mohan
Messages: 3
Registered: August 2002

Junior Member

Hi Noches Bellas

In the query you have to user minus.

select user_id,name from boat_users
where user_id in (select user_id from boat_users
minus select user_id from boat)

Mohan

Report message to a moderator

Re: Can you help with this simple SQL query? [message #21679 is a reply to message #21653]

Thu, 22 August 2002 06:45

Son Nguyen
Messages: 8
Registered: July 2002

Junior Member

A simple outer join should work I believe:

select bu.user_id from boat_users bu, boat b where bu.partnum = b.partnum(+) and b.partnum is null;

Report message to a moderator

Previous Topic:	URGENT!! Lenght of the data
Next Topic:	trapping no records on update

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 19:54:31 CDT 2024