Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Compare valid data from two tables Post Reply Post New Topic
Author Message
VStevens
Newbie
Newbie


Joined: 24 Dec 2008
Online Status: Offline
Posts: 12
Quote VStevens Replybullet Topic: Compare valid data from two tables
    Posted: 25 Mar 2009 at 12:36pm
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!






IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Mar 2009 at 5:22am
Try a double left join on both division and ID giving all values from worker table. Then place the ID from company table next to the fields you want displayed. It should be null for the invalid workers. If that is working just conditionally suppress as that ID not null.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.