table join question

[yehong]

Is it possible to link one Date field to two Date fields in another table with two different join types?
Example:
first link: Table1.Date >= Table2.StartDate
second link: Table1.Date < Table2.EndDate

I tried it and Crystal linking expert just creates same join type for both Date fields of Table2.

Thanks for any ideas or tips.

 

[lbass]

Yes, you can't do that--as you've discovered, the joins between two tables are always of the same type. But why not link the tables on another field, e.g., a unique ID field, and then use a record selection formula like:

{Table1.Date} >= {Table2.StartDate} and
{Table1.Date} < {Table2.EndDate}

Usually, one would want to include the ending value, in which case you should change the last sign to &quot;<=&quot;.

-LB

 

[yehong]

Table2 does not have any other unique field that I can link it to Table1 and then pass date joins as record selection.
Is it possible if I do an equal join on Table1.Date=Table2.StartDate and then in the record selection I speciy >= and < conditons? Will it give me same results?

 

[synapsevampire]

You can probably do this, but it is version dependent.

Once you've created the joins in Crystal, you can probably manually edit the SQL by selection Database-Show SQL Query.

-k

 

[lbass]

I think if you use an equal join, you will ONLY return dates that match in both tables, so I don't think you could get dates greater than or less than. You could use a >= link for startdate only and then in the record select, use {table1.date} < {table2.enddate}.

-LB

 

[yehong]

how about if I have an equi join on both tables, and add this in the record selection formula, will it return same results?

{Table1.Date} ='2001-01-01' and
{Table1.Date}>={Table2.StartDate} and
{Table1.Date} < {Table2.EndDate}

 

[lbass]

No. An equal join between two date fields will result in only those records from both tables with the same date. So, if you used {table1.date} and did an equal join with {table2.startdate} then you would only get records back from each table where there was a corresponding date in the other, as in:

Table1.date Table2.startdate
7/01/2003 7/01/2003
9/23/2003 9/23/2003 //etc.

Other non-matching dates in each table would be excluded just based on the join, even BEFORE the record selection criteria was applied.

If you are unable to use these other types of joins, you should tell us that.

If only equal joins are available, then maybe you have a third table that could act as a &quot;mediator&quot; as in:

Table1(Jobs) Table2(Services) Table3(Mediator)
JobsID ServicesID JobsID
Date StartDate ServicesID
EndDate MediatorID

If you have a table like table3, then you could create a link from table3 to table1 on the JobsID field and from Table3 to table2 on the SerivcesID field. Then you could easily do the date selects that you want to do.

-LB

 

[yehong]

Thank you all for your help in fixing this problem. I tried LBass's previous approach
use a >= link for startdate only and then in the record select, use {table1.date} < {table2.enddate}.
so far its working ok.