Skip to Content
0

Display all records missing from left joined table

Jan 30 at 07:54 PM

25

avatar image
Former Member

Table A (CALLLOG) has a data column called CALLID. Table B (JOURNAL) also has a CALLID data column but only for tickets with journal entries (tickets can have multiple entries which are sequenced by data column JOURNAL.SEQ). I need to display all CALLID's that DO NOT have any Journal Entries (do not exist in Table B). I have this in a SQL Query but cannot convert this to Crystal. SQL Statements is as follows:

SELECT CALLLOG.CALLID, JOURNAL.SEQ

FROM CALLLOG LEFT JOIN JOURNAL ON CALLLOG.CALLID = JOURNAL.CALLID

WHERE JOURNAL.SEQ is null;

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Brian Dong Jan 30 at 08:23 PM
0

Hi Adam,

Link your tables in Crystal with the Left Outer Join. In the Record Selection Formula create a formula like:

ISNULL ({JOURNAL.SEQ}); 

If you have your report option set to convert NULLS to Default then your formula would look like:

{JOURNAL.SEQ} <> 0; 

If your SEQ is a number or

{JOURNAL.SEQ} <> ""; 

If your SEQ is a string.

Good luck,

Brian

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Excellent, the first option did it. I had not(isnull({JOURNAL.SEQ}) in the formula field for some reason. Thank you so much!

0