Multiple querres 1

[FionaCondon]

Hi all
I am writing a report in Crstal Reports 8.5 which contains information from a number of linked tables. Somewhere along the line I come to a point where there may or may not be a corresponding record. I put a left outer join in here and I got the record displayed whether or not there was corresponding record to the right of the join. But when I try to inlcude data from tables to the right of this link, I lose the lefthand record. I thought the way round this would have been to base the report on queries rather than tables (this is what I would have done in Access) but I can't find any way of doing this. I have tried creating a query with the SQL Designer but as far as I can see I can't then use the query as the basis for another query in SQL Designer, and in Crystal Reports, I can only use tables on their own or a single query. Am I missing something? Is there another way to deal with the problem or another way of using multiple queries or queries mixed with tables?
Fiona

 

[synapsevampire]

The SQL Designer isn't suggested, even by Crystal Decisions.

When you're adding criteria to the child of a LO join, you are limiting the query to only rows matching that criteria, so use 2 criteria:

isnull({table.childfield})
or
{table.childfield} = "somevalue"

This may resolve.

You might also resolve this by creating a view/sp on the datbase side.

-k

 

[lbass]

Once you have used a left join any tables linked to the right of the left joined table must also be linked with a left join.

On a slightly different note:

SV-I'm with Ken (thread149-694454). Even using your two criteria, you will lose those records in the left table where there is a corresponding record in the right table, but one that does not meet your criterion--resulting in only partial records from the left-most table. A left join would produce records like:

{Table1.ID} {Table2.ID} {Table2.Value}
001 (null) (null)
002 002 A
003 (null) (null)
004 004 C

If you use a select like:

isnull({Table2.Value}) or
{Table2.Value} = "C"

your result would be:

{Table1.ID} {Table2.ID} {Table2.Value}
001 (null) (null)
003 (null) (null)
004 004 C

...and you have lost {table1.ID} = 002.

-LB

 

[JohnEck]

If you are dealing with a very complicated join especially with multiple tables on the right of a left join it is usually easier to build an SP on your database. Be careful though if you are upgrade to CR 9.0 it is very twitchy when it comes to SPs. It created huge headaches for us when we upgraded to 9.0

 

[FionaCondon]

Thanks for your help - I have used a subreport which seems to be the best solution given the circumstances. However, I would like ot suppress printing of the address in the subreport if it is the same as the address in the main report and I can't work out how. Any ideas? Also - what's SQL Designer for if it's not recommended?
Fiona