selecting when a value doesn't not exist in a second taable

[Former Member]

In the below example I have two tables where the Key field in table 1 and the RelatedKey in Table 2 have a Left out join from table 1 to table 2.   The issue I'm trying to solve is finding the data in the table 1 Key field where the world Yellow doesn't exist in Table 2 color field.  Table 2 may have multiple rows with the same relatedkey and multiple colors.

In the below example the result should be Table1.Key = 123 (since Yellow doesn't not exist for relatedkey 123)

Table 2
RelatedKey	Type	Color
123	car	red
123	car	green
123	car	Blue
124	car	Yellow
124	car	green
124	car	Blue

the

Table 1

Key

123

124

Table 2
RelatedKey	Type	Color
123	car	red
123	car	green
123	car	Blue
124	car	Yellow
124	car	green
124	car	Blue

[Former Member · Accepted solution]

Thanks, but that result gave all the records the have "Yellow", I need all RelatedKeys that do not have the word Yellow in it.

[DellSC · Accepted solution]

Ok, then change the "= 'Yellow'" to "<> 'Yellow'".

-Dell

[Former Member · Accepted solution]

Not the desired result that gives Key 124 with green and blue and the Yellow missing.  I need Key 123 because Yellow does not exist in the rows with a key 123.

[abhilash_kumar · Accepted solution]

You should perhaps report off of a SQL query via the 'Add Command' option:

Select T1.Key From Table1 T1

Where EXISTS (Select 1 FROM Table2 T2 Where T1.Key <> T2.RelatedKey AND T2.Color = 'Yellow')

-Abhilash

[Former Member · Accepted solution]

Thank you for the reply, but I'm looking to perform within Crystal Reports, not within a SQL command.

[abhilash_kumar · Accepted solution]

Go to the Field Explorer > Right click 'SQL Expression' > New > Paste this code (change table and field names):

(

Select "T1"."Key" From "Table1" "T1"

Where EXISTS (Select 1 FROM Table2 T2 Where "T1"."Key" <> T2.RelatedKey AND T2.Color = 'Yellow')

)

Next, go to the Record Selection Formula and paste this:

{Table1.Key} = {%SQL Expression}

-Abhilash

[Former Member · Accepted solution]

Thank you but I do not understand the SQL command. What is T1 and T2?

[former_member292966 · Accepted solution]

Hi Robert,

There are two ways to do this.  The most efficient way would be to have a view or stored procedure to have your database do this for you.  Doing it in Crystal is possible but requires some almost complex grouping and subreports to determine if a Related Key has a color or not. 

To have this done in Crystal, the best way would be to have a Color table.  Then you could do a Left Outer Join between Table2 and Color and get your results with the Record Selection formula like Dell showed. 

The problem is Crystal can only show you what data is retrieved and not what is missing.  Because there is no link between color and RelatedKey, you will need to do a lot of complex reporting that will include subreports to know what is missing from your resultset. 

Hope this helps,

Brian

[DellSC]

Here's what you'll add to the formula in the Select Expert to get the data you want:

(

IsNull({Table2.RelatedKey}) or

{Table2.Color} = 'Yellow'

)

NOTE:  If you have any other selection criteria, you MUST have the parentheses around this or it won't work right.

-Dell