[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2341
  • Last Modified:

display records that exist in table a but do not exist in table b

Hi There,

I'm using Crystal Reports XI.

I've got two tables and want to create a report which displays all the records in table a that do not exist in table b.

Is this possible?

thanks! Kristin
0
forcedexposure
Asked:
forcedexposure
  • 2
  • 2
1 Solution
 
mlmccCommented:
Yes it is.

There are 2 ways
1.  Use a query with a left outer join from A to B.  Can be done by selecting the tables and setting the join to LEFT OUTER

You then suppress any records where B.ID is NOT null
Not(IsNull({TableB.IdField})

2.  Use a Crystal command with a subselect as

SELECT TableA.LIST OF FIELDS
FROM TableA
WHERE (Not (TableA.ID IN (SELECT TableB.ID FFROM TableB)))

mlmcc
0
 
forcedexposureAuthor Commented:
I want to go with option # 1 because i don't have the luxury of creating views in my tables.
but option #1 doesn't work... it's displaying all the records from table b that don't match table a. So there are 4012 lines of data when there are only 59 records in table a.

help?
0
 
mlmccCommented:
A Crystal command is built in Crystal not in the database.

Can you upload the report

mlmcc
0
 
James0628Commented:
Assuming that you used the correct field(s) to link table A to table B, in that direction, and made it a Left Outer join, then it seems like mlmcc's suggestion should have worked.  I believe you could also use IsNull ({TableB.Field}) in the record selection formula (instead of using suppression).  I've done that before for just this kind of thing and it worked for me.

 James
0
 
forcedexposureAuthor Commented:
Hi There! I reread your suggestion and got it to work. My problem was that i needed to do left outer joins on two sets of fields from Table A to Table B.


Thanks for your patience and your help!!


best wishes, Kristin
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now