Problems with lookup table and values table - finding unmatched record 3

[craigwoods]

Hi

This problem has been driving me potty for some time now! I have an SQL Server 2005 database and am using CR XI.

I need to produce a report that is printed copy of the information held on the database. The report will show all of the valid options that are available to be selected and then tick off the details that have been selected on the database.

To do this i thought i would use a Left Outer Join between the lookup table and the data table, but all that happens is that only the matching data is returned and not the unmatched data.

For example:

Lookup
Strategic ID, Description
1, Safe
2, Healthy
3, Economic
4, Enjoy
5, Positive

Table:
Project ID, Strategic ID
1, 1
1, 2
1, 3
1, 4
2, 2
2, 3


What I get is

1, 1
1, 2
1, 3
1, 4
2, 2
2, 3

But what i want is
1, 1
1, 2
1, 3
1, 4
null, 5
null, 1
2, 2
2, 3
null, 4
null, 5

There are no selection criteria at the moment.

I think i'm missing something major, but i'm not sure what!!

Any help gratefully appreciated.

Craig

 

[craigwoods]

I think that i may have a solution for this. I've been playing about this morning and i think that the problem has been caused by the sheer amount of data in the project table.

I think that my query was correct, but when Crystal was pulling the records back it was finding records for each strategic objective, albeit that they were linked to different projects.

i.e.

Projects ID, Strategy ID
1, 1
1, 2
1, 3
2, 4
3, 5

So there are no null values, as if you pick up all of the values of Strategy ID, there is a matching project id.

So what i've done is...

Added a command to the data source that picks out one project. I have then used the strategies lookup with a left out join to this command table.

Here's the code for my command:

SELECT "projects_strategic_objectives"."project_id", "projects_strategic_objectives"."strategic_objective_id"
FROM "ims_staffs"."dbo"."projects_strategic_objectives" "projects_strategic_objectives"
WHERE "project_id" = {?ProjectID}

This does seem to work and achieves the result I want - whether this is good practise I don't know.

Thanks for your help guys

 

[lbass]

Now that you lay it out like that, I think that is exactly the issue, and I'm sorry I didn't pick up on that. If there were a project ID in the strategies table, you could have added a left join on the projects ID, and then I think you would have gotten the desired results.

Glad you were able to figure it out on your own.

-LB

 

[craigwoods]

Many thanks to everyone who provided assistance

Craig