Looking for a way to implement the following situation
I need to select entries form TableA and TableB. The join needs to
happen using two separate fields. One of this fields can be used in
the normal way but the other filed needs some special logic
The normal join field is ProdID and the unusual one is PriceTable. If
there are no entries in Table B for the values of ProdID and
ProceTable in the TableA entry then the join must happen using the
value TableA.ProdID and a constant value "STANDARD" to join the
PriceTable entries in TableB to TableA
Here's an example
TableA.ProdID TableA.PriceTable TableB.ProdID Tableb.PriceTable
1 SPECIAL 1
SPECIAL
1 SPECIAL 1
SPECIAL
1 STANDARD 1
STANDARD
2 SPECIAL 2
STANDARD
2 SPECIAL 2
STANDARD
For ProdID 1, the entries in TableB wth ProdID 1 and PriceTable
SPECIAL should be selected. The entry in TableB for ProdID 1 and
PriceTable STANDARD Should NOT be selected
For ProdID 2, the entries in TableB with ProdID 2 and PriceTable
STANDARD should be selected
I've tried JOIN with CASE, WHERE with CASE, compound SELECTs with
UNION, UNION ALL, INTERSECT and EXCEPT, but haven't managed to figure
out how to make this work.
Can this be done in a single SELECT?
Pete Haworth
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
