Print Page | Close Window

Compare valid data from two tables

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5896
Printed Date: 08 May 2024 at 12:27pm


Topic: Compare valid data from two tables
Posted By: VStevens
Subject: Compare valid data from two tables
Date 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!


Replies:
Posted By: DBlank
Date 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.


Print Page | Close Window