Home » SQL & PL/SQL » SQL & PL/SQL » select statement help
select statement help [message #359134] Thu, 13 November 2008 18:53 Go to next message
jman21
Messages: 2
Registered: November 2008
Junior Member
I can't seem to figure this select statement out. I have two tables(Employee and Mailbox). There is a common attribute called EMP_NUM in both tables. I want to select only those Emp_Nums that are in the Employee table but not in the Mailbox table. Here is what I have been trying and it isn't working.
SELECT EMPLOYEE.EMP_NUM
FROM EMPLOYEE JOIN MAILBOX ON EMPLOYEE.EMP_NUM != MAILBOX.EMP_NUM
Re: select statement help [message #359146 is a reply to message #359134] Thu, 13 November 2008 19:57 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
Hmmmm... So, you want a result set consisting of all the emp_nums in one table WHERE the emp_nums are NOT IN another table?

Despite the bad pun in the above hint, there is insufficient information in your problem specification. You have included no information about the potential nullity of the emp_num column. It will be instructive to examine why the above hint fails in the presence of nulls.

However (and I am assuming here that this is some kind of homework problem; please excuse me if it is not) I will instead discuss what is wrong with your attempted solution. Suppose our tables are as follows (where they only have a single column each, EMP_NUM:

     EMPLOYEE
       -----
         A
         B
         C

      MAILBOX
        ----
         B
         C
         D


Now, conceptually, a join is accomplished as follows: First, form the Cross Join between the two tables, and then eliminate those rows filtered out by the predicates. So, we first form the product (where, to make it easier to see what is happening, I have kept both columns):
    1.  A B
    2.  A C
    3.  A D
    4.  B B
    5.  B C
    6.  B D
    7.  C B
    8.  C C
    9.  C D


And keep only those rows which match our filter, which is where the row_nums are different. Which means, we keep rows 1,2,3,5,6,7,9, where I think you only want 1, 2, and 3. Clearly, if you are going to solve this with a join (which I personally would not do) you are going to have to chose a different relational operator for your join condition, something which relates the existence of rows between the tables.
Re: select statement help [message #359147 is a reply to message #359146] Thu, 13 November 2008 20:17 Go to previous messageGo to next message
jman21
Messages: 2
Registered: November 2008
Junior Member
All of the Emp_Nums in the table MAILBOX are in the table Employee, but there are some Emp_Nums in Employee that are not in table Mailbox. I want to select the Emp_Nums and another attribute that are in the Employee table but not in Mailbox table.
Re: select statement help [message #359154 is a reply to message #359134] Thu, 13 November 2008 22:01 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I would prefer NOT EXIST over NOT IN

Smile
Rajuvan.
Re: select statement help [message #359188 is a reply to message #359134] Fri, 14 November 2008 01:12 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
Very convinient in such cases is the MINUS Operator:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries004.htm#i2054381

[Updated on: Fri, 14 November 2008 01:14]

Report message to a moderator

Re: select statement help [message #359191 is a reply to message #359134] Fri, 14 November 2008 01:21 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

If you use minus, you have to another sub query to get the OP's required result.

Quote:
I want to select the Emp_Nums and another attribute that are in the Employee table but not in Mailbox table.



Smile
Rajuvan.
Re: select statement help [message #359426 is a reply to message #359147] Sat, 15 November 2008 20:06 Go to previous message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
jman21 wrote on Thu, 13 November 2008 20:17
All of the Emp_Nums in the table MAILBOX are in the table Employee, but there are some Emp_Nums in Employee that are not in table Mailbox. I want to select the Emp_Nums and another attribute that are in the Employee table but not in Mailbox table.


Here, I was thinking that you were trying to do an anti-join. But, your last quote suggests that maybe you are really trying to do an outer join. So, rather than keep guessing, I'm going to to paraphrase Michel (and I don't have his experience, so I won't do it as well as he does) and ask you to:


  1. Provide some DDL for your problem
  2. Provide some sample data
  3. Give the results you want
  4. Give your best try, and the results it is giving, and
  5. Properly format all of the above


#1 is important, as the potential nullity of your EMP_NUM columns will affect the result.
Previous Topic: convert GMT TO day light saving time
Next Topic: Impact on killing session
Goto Forum:
  


Current Time: Fri Dec 02 20:34:12 CST 2016

Total time taken to generate the page: 0.19243 seconds