?
Solved

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

Posted on 2014-04-23
5
Medium Priority
?
2,002 Views
Last Modified: 2014-04-28
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
Comment
Question by:forcedexposure
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 40018331
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
 

Author Comment

by:forcedexposure
ID: 40018729
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40019087
A Crystal command is built in Crystal not in the database.

Can you upload the report

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 40019606
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
 

Author Closing Comment

by:forcedexposure
ID: 40027307
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question