select statement help [message #359134] |
Thu, 13 November 2008 18:53  |
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   |
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   |
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 #359426 is a reply to message #359147] |
Sat, 15 November 2008 20:06  |
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:
- Provide some DDL for your problem
- Provide some sample data
- Give the results you want
- Give your best try, and the results it is giving, and
- Properly format all of the above
#1 is important, as the potential nullity of your EMP_NUM columns will affect the result.
|
|
|