On Tue, Oct 27, 2009 at 12:43:56PM -0700, Peter Haworth scratched on the wall:
> 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.
Then you'll get multiple matches and multiple rows returned for each
value.
> 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
I think the tail of your TableB data is incorrect. For starters, you
don't have a 3/STANDARD in TableB, or a 3/anything, for that matter.
> 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.
Yes-- there was an assumption that (ProdID, PriceTable) was unique
for each table. If you have multiple (ProdID, PriceTable) instances
in TableB (Such as 1/SPECIAL, above), then TableA will join against
each of those. In this case that would/should return 3 rows. However,
since my solution joins TableB in twice, those 3 rows (from the first
JOIN) are matched again in the second JOIN, producing 3x3=9 results.
You see something similar with 1/STANDARD producing 2x2=4 results.
If (ProdID, PriceTable) is not unique, then it should return the 3
rows produced by a single (valid) join. To get rid of the second set
of duplicates (which should only exist if a direct match was found in
the original join) just add an additional condition that
B1.rowid = B2.rowid to force the second set of TableB rows to match
up exactly if (and only if) the B1.rowid is valid and has not already
made the required number of duplicate A1 rows. This basically makes
sure the B2 data is the exact same as the B1 row if a valid B1 row exists.
We need to shift the case statement around a bit so it returns the
conditional value itself and not the test value, but consider:
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
...> CASE B1.PriceTable
...> WHEN B1.PriceTable THEN
...> A1.PriceTable = B2.PriceTable AND B1.rowid = B2.rowid
...> ELSE
...> 'STANDARD' = B2.PriceTable
...> END );
I think that will do something closer to what you want.
-j, now my brain hurts
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
