How do I do this in Crystal

[michaelng]

Hi,

Table1:

1 dog
2 cat
3 turtle


Table2:

1 teeth
1 jaws
2 teeth
2 jaws
3 teeth
3 jaws

Table3:

1 bones
1 vegetables
2 bones
2 vegetables
3 vegetables

I need to do a query that would select only one row of table1. I am trying to fix a report that is keying on table2 and table3 using "teeth" and "bones" as identifier, but whoever created it in the first doesn't realize that not all rows in table1 uses those two identifier; thus "turtles" from table1 is left out.

I can easily do it in MS Access by creating another table with a selection of "first" or "last" grouping on table2 and table3, then joining it again. I am not sure how I can do this in Crystals since I am fairly new to it.

Basically, I want to do a query with one unique row of table1 to appear regardless what type they have in table2 and table3. I know I can just not join table1 with table2 and table3 and get the same result, but I need other information in table2 and table3 that requires me to join them.

Any help would be appreciated...

Thanks

[wapper]

Did I get it right? you want to have all animals, and if an animal has teeth, you want to see the word "teeth", and if it eats bones, you want to see "bones"?

DOG teeth bones
CAT teeth bones
TURTLE teeth

Difficult to solve without creating extra views or altering SQL query (bad practice). Or without using subreports (even worse in terms of performance but worth trying).
Crystal does not support adding custom conditions to join expressions. In MSSQL, it would look like:
SELECT a.name, w.name, f.name FROM animal a
LEFT OUTER JOIN weapons w ON a.id = w.id AND w.name = 'teeth'
LEFT OUTER JOIN food f ON a.id = f.id AND f.name = 'bones'

But you cant do this in Crystal. At least not in CR8 I am using.
However, in CRXI (maybe CR10 too) you can create a so-called command that is basically a dynamic view based on actual data. You can create two commands "weapons" and "food" that will only return "teeth" and "bones" rows, and link those commands with table "animals" using outer join. This should get you what you want.

[michaelng]

Thank you for taking to time to reply.

Yes, basically that's what I want, to pull a row from table1. Since I had to join it with two other tables that could cause duplicate data, I would have to limit it by something.

I have CR 8.5, so probably I don't have any easy solution from Crystals itself; but I would have to create a view or subreport---that's what I was afraid of.

In MS Access, I would just have had to create another query and then do a join. I supposed this is similar to the command feature you just describe for the CRXI and CR10.

[wapper]

If you are using some client-server database platform, like MSSQL, I'd recommend creating 2 views to serve only for this report. A view is same for MSSQL as a query for MSACCESS. But if you are stuck with some DBase-like solution, then you are really out of luck.

Command feature in newer Crystal is not an ideal solution for cases like this, because joining actually occures on the client, not server. So for large tables, the report can be slow. I would prefer being able to adjust join expressions in Crystal manually if needed.

[michaelng]

Hi Wapper,

Thanks for your help. I was able to do this by creating views in MS Sql and I had to create two views since table2 and table3 uses different keys that are in table1.