CR with Left Joint and where condition

I have two tables like this

Table 1

tid
...

Table 2

tid
type
...

the relation is on the column tid of both the tables. We can have multiple records for a tid in Table2 or we may not have any record too.

I am able to get results from Table1 and Table2 by making left outer join in crystal reports (linking). But i have one problem.
I need to get only records in Table2 whose type (field) is "A". For this is achive I am going to select expert in Crystal reports and did type="A" but the problem is if Table2 is not having any records for a tid that tid record is not displaying. How to solve this issue.
I know if we do some thing like this type (+)="A" it will work but How can I do this in Crystal Reports.

Any help is apperciated.

Thanks
KalikiYou can use the add command property in the database expert and write manually your database query

select t.field1, r.field1, t.field2, r.field2
from tab1 t, tab2 r
where tab1.field3 = tab2.field3 (+)
and tab2.field4 (+) = 'A'

This works fine using CR version 10, if you are using an older version (8.5) you can type the 'and tab2.field4 (+) = 'A'' part of the condition to the SQL-query (DATABASE, EDIT SQL-query).

- Jukka|||Thanks for your response.
I am using 8.5 so i am editing the sql via (DATABASE, EDIT SQL-query).

counting a pyramid db

hiya all,
I got a table that is represented as a B-Tree, it has a one to
many relation with its self
----
| UserID |
----
|FatherID|
----

UserID = FatherID
it looks like this
o
/// \\\
ooo ooo
//\\\ ///\\\
oo ooo ooo
I want to get all the nodes that has 256 children and grand children and each node is limited to have 6 nodes under it.
any ideas ? the DB isn't filled with data yet so we can add more fields
to the table if it helps.

I could write a trigger that increments a counter in each node but it will be a recursive trigger so it can go from the child to its father and the father to its father upating them and so on.

regards
Ahmassuming this structure in your DB
UserID INT Primarykey
FatherID INT
Counter Int

You may go for a while loop in a trigger to update counters of father and forefathers.

Originally posted by elamor

UserID = FatherID
it looks like this
o
/// \\\
ooo ooo
//\\\ ///\\\
oo ooo ooo
I want to get all the nodes that has 256 children and grand children and each node is limited to have 6 nodes under it.
any ideas ? the DB isn't filled with data yet so we can add more fields
to the table if it helps.

I could write a trigger that increments a counter in each node but it will be a recursive trigger so it can go from the child to its father and the father to its father upating them and so on.

regards
Ahm|||theguru, thanks for the reply I know how to do that but I'm looking for
a better and an optimized why to do it. imagine having 1000 new inserted records and the trigger is fired to update all the parents
what could happen to the DB server ? :) I think you got the idea now

regards|||ok I too have simmilar problem.so i am looking forward for a better way.
I will give u the code i am currently using in my next reply.
thanks.

Originally posted by elamor
theguru, thanks for the reply I know how to do that but I'm looking for
a better and an optimized why to do it. imagine having 1000 new inserted records and the trigger is fired to update all the parents
what could happen to the DB server ? :) I think you got the idea now
regards|||one thing with recursive triggers is the absolute necesity to check for the recurse.
dont forget to test for @.@.nestlevel which will give you your countdown before 32 levels.|||Have you considered switching to the nested sets (http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci801943,00.html) model? It is a two-edged sword, in that it puts the processing time up front to construct the sets, but since queries are usually much more frequent than builds, that isn't a big problem.

-PatP|||I've created many recursive relationships in my databases, but I've found that recursive triggers in SQL are not nearly as efficient as accumulating changes in a table variable and then updating all the records in the b-tree with one join to the table variable.|||seems that it's not working at all I tried different way of designing the
db but with no luck , I'll get back when I find a good answer for this

and thanks for all of you who replied

regards
Ahm