Joined: 24 Dec 2008
Online Status: Offline
Posts: 12
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?
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
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.
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