05-15-2008, 04:42 PM
|
|
Junior Member
|
|
Join Date: May 2008
Posts: 2
|
|
ANY dialect question:
"Condition table" query
I have a reference table 'LOOKUP' with 4 fields and sample values as follows:
HTML Code:
Plant Group WorkCentre WageType CalcType
1234 1002 W001 9Y10 A
1234 * W002 9Y11 C
1234 1005 W001 * D
1234 1002 W003 * C
1234 1007 * * C
1234 1002 * 9Y32 C
etc.
There is another table with transaction values:
HTML Code:
Plant Group WorkCentre WageType Date
1234 1002 W003 9Y13 15/05/08
1234 1005 W001 9Y13 15/05/08
etc.
I need a query which will return all records in the transaction table which find a match for a given CalcType in the Lookup table, with * considered as a wildcard in the lookup table (i.e., any value is OK in the transaction table as long as the other specific values match in the lookup table).
The problem is that more than one field value could be a '*' in the lookup table, as shown above.
Is it at all possible via SQL to retrieve the transactional records which find any match for a given CalcType in the lookup table? Or am I constrained to use procedural logic, examining each transaction against every record in the lookup table until I find a match or run to the end?
Would appreciate any ideas...
|