Thanks for this. I tried the CASE solution in preference to the
coalesce solution since I'm more familiar with CASE than coalesce.
Your statement that there must be matching TableB entries with
PriceTable STANDARD for all values of TableA.ProdID is correct.
It almost works but not quite. I believe that at least part of the
reason for this is that, as you commented, there can be multiple
entries in TableB that match any single ProdID/PriceTable in TableA.
Here's what I experience. with the following data
TABLEA.PRODID/PRICETABLE TABLEB.PRODID/PRICETABLE
1/SPECIAL 1/SPECIAL
1/SPECIAL
1/SPECIAL
1/STANDARD 1/STANDARD
1/STANDARD
2/STANDARD 2/STANDARD
3/SPECIAL 2/STANDARD
2/STANDARD
The TableA 2/STANDARD and 3/SPECIAL entries return the correct TABLE
entries. The TableA entries for 1/SPECIAL and 1/STANDARD return
duplicated TableB entries. In the case of 1/SPECIAL, the 3 TableB
entries are returned 3 times for a total of 9 entries. In the case of
1/STANDARD the 2 TableB entries are returned twice for a total of 4
entries.
The 1/STANDARD and 2/STANDARD are puzzling since they both match on
the same JOIN yet 1/STANDARD returns duplicates and 2/STANDARD doesn't.
I hope I'm explaining this well enough - it's somewhat complicated!
Pete Haworth
On Oct 27, 2009, at 5:00 AM, sqlite-users-requ...@sqlite.org wrote:
> sqlite> SELECT A1.*, B2.*
> ...> FROM TableA AS A1
> ...> LEFT JOIN TableB AS B1
> ...> ON ( A1.ProdID = B1.ProdID AND A1.PriceTable =
> B1.PriceTable)
> ...> JOIN TableB AS B2
> ...> ON ( A1.ProdID = B2.ProdID AND B2.PriceTable = (
> ...> CASE B1.PriceTable
> ...> WHEN B1.PriceTable THEN
> ...> A1.PriceTable
> ...> ELSE
> ...> 'STANDARD'
> ...> END ) );
> 1|STANDARD|1|STANDARD
> 1|SPECIAL|1|SPECIAL
> 2|SPECIAL|2|STANDARD
>
> This is the same basic idea, in that we join TableA to TableB twice.
> The difference is that we only want the results of the second join
> (T1/B2) where we join against A1.PriceTable if we know we got a valid
> match the first time, or 'STANDARD' if we did not. The key to making
> that work is that ( B1.PriceTable = B1.PriceTable ) will be FALSE
> (and return the ELSE value) if B1.PriceTable is NULL.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
