Skip to Content
0
Former Member
Mar 25, 2009 at 07:53 PM

filter valid data from two tables

68 Views

We have one table (COMPANY_INFO) that has two columns. One lists company id and the other column is acceptable divisions for that company. The divisions are not the same format

We have another table (PERSON) that lists person name, company and division.

However, at times the division listed in the second table is not always one of the acceptable ones for that company. I am trying to output all instances where a person is listed for an unacceptable division for a particular company.

for example:

COMPANY_INFO
ID       Div
1        A
1        B
2        AB
2        CD
3        A
3        1
3        2

 
PERSON
NAME         ID           DIV
G_smith      1             B
H_jones       2             EE
P_jobs        2             AB
M_barnes    3             A
L_appple     3             B

I would want it to return:

 
NAME          ID            DIV
H_jones        2             EE
L_appple       3             B

because "EE" is not an acceptable DIV value for ID 2 and "B" is not acceptable DIV value for ID 3

I tried changing some of the linking options, to be not equal, but that didn't work.

I would prefer to avoid using subreports for performance considerations. Any suggestions?

Thanks for your help!