Linking tables with a range value...?

[MJRBIM]

Crystal Reports 10 (Professional), Pervasive 8 SQL database.

I'm having trouble finding a good way to link these two tables....

The RECORD exists as multiple rows in the ITEM_DETAIL table, but only as a single row in the ITEM_TYPE table. If the RECORDS strings are Equal and the Item_Number is in the range set by Item_Start and Item_End.

(TABLE = ITEM_DETAIL)
RECORD ITEM_NUMBER PRICE
------------------------------------
ABCD 1 10.95
ABCD 2 8.95
ABCD 3 7.25
ABCD 4 10.95
ABCD 5 6.75
ABCD 6 9.99
FGHI 1 7.75
FGHI 2 6.58
FGHI 3 8.23
------------------------------------

(TABLE = ITEM_TYPE)
RECORD ITEM_START ITEM_END ITEM_NAME
-----------------------------------------------
ABCD 1 4 Hammer
ABCD 5 6 Shovel
FGHI 1 3 Saw
-----------------------------------------------

The data set I am try to accomplish with this link would look like this....

RECORD ITEM_NUMBER PRICE ITEM_NAME
-----------------------------------------------
ABCD 1 10.95 Hammer
ABCD 2 8.95 Hammer
ABCD 3 7.25 Hammer
ABCD 4 10.95 Hammer
ABCD 5 6.75 Shovel
ABCD 6 9.99 Shovel
FGHI 1 7.75 Saw
FGHI 2 6.58 Saw
FGHI 3 8.23 Saw
-----------------------------------------------

If anyone has a smart way of linking these table, please let me know.

Thanks in advance for the help.....!

 

[synapsevampire]

Perhaps you couldstate what these records STRINGS are using actual field names.

Pervasive is a bit tricky, but a Add Command could return the data like this, or you might try using grouping in the Crystal Report to accomplish this by joining on the RECORD (assumed to be the STRING), and in the Report->Selection Formulas->Record place:

(
{Item_Detail.Item_Number} >= {item_type.Item_Start}
and
{Item_Detail.Item_Number} <= {item_type.Item_End}
)

Unlikely this will be processed by the database though.

An Add Command might look like:

select * from Item_Detail
where Item_Detail.record = {item_type.record
and
Item_Detail.Item_Number >= item_type.Item_Start
and
Item_Detail.Item_Number <= item_type.Item_End

I'm basing this on standard SQL practices, Pervasive may puke on it.

-k

 

[synapsevampire]

ooPS, FORGOT THE item_type in the From:

An Add Command might look like:

select * from Item_Detail, item_type
where Item_Detail.record = {item_type.record
and
Item_Detail.Item_Number >= item_type.Item_Start
and
Item_Detail.Item_Number <= item_type.Item_End

-k