Jay, First, yes I screwed up on the table data examples. The 3/SPECIAL TAbleA values should have shown 2 3/STANDARD TableB entries. My brain is hurting too!
Anyway, the main thing is that your latest suggestion works perfectly so thanks for your help, I appreciate it. Pete On Oct 28, 2009, at 5:00 AM, sqlite-users-requ...@sqlite.org wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
