Skip to Content
avatar image
Former Member

Display all records missing from left joined table

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;

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jan 30 at 08:23 PM

    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

    Add comment
    10|10000 characters needed 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!