On Mon, Oct 26, 2009 at 05:32:27PM -0700, Peter Haworth scratched on the wall: > 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 >
You appear to have duplicate rows in your example, but here are two different ideas. We'll start with this: sqlite> CREATE TABLE TableA ( ProdID, PriceTable ); sqlite> CREATE TABLE TableB ( ProdID, PriceTable ); sqlite> sqlite> INSERT INTO TableA VALUES ( 1, 'STANDARD' ); sqlite> INSERT INTO TableA VALUES ( 1, 'SPECIAL' ); sqlite> INSERT INTO TableA VALUES ( 2, 'SPECIAL' ); sqlite> sqlite> INSERT INTO TableB VALUES ( 1, 'STANDARD' ); sqlite> INSERT INTO TableB VALUES ( 1, 'SPECIAL' ); sqlite> INSERT INTO TableB VALUES ( 2, 'STANDARD' ); First idea: sqlite> SELECT ...> A1.ProdID, ...> A1.PriceTable, ...> coalesce( B1.ProdID, B2.ProdID ), ...> coalesce( B1.PriceTable, B2.PriceTable ) ...> 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 'STANDARD' = B2.PriceTable ); 1|STANDARD|1|STANDARD 1|SPECIAL|1|SPECIAL 2|SPECIAL|2|STANDARD As I understand it, you're basically trying to match both a ProdID and a PriceTable, but if a PriceTable cannot be found, you want to use the 'STANDARD' PriceTable. This basically JOINs TableA to TableB twice, one with an exact match and one with a 'STANDARD' match. The first match (A1/B1) is done as a LEFT OUTER JOIN, so any mis-matched rows are matched to NULL on the B1 side. The second match (A1/B2) is done as a standard INNER JOIN and assumes there will always be a 'STANDARD' row in TableB for every possible ProdID in TableA. We then use the coalesce() function to return either the valid exact-matched values from B1. If those are NULL, we return the 'STANDARD' match rows from B2. Here is another apprach that uses a CASE statement: 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. I'm sure there are a few other ways you could factor this problem using a double-join, but that's one baisc approach. The heart of the issue is that there is no way to test if a row has a match or not within a single JOIN. You must do the LEFT JOIN to see if there was a match or not. Then, based off the information of that JOIN, you can do the JOIN again and decide to take the values from the first match (if they're valid) or re-try with other values if they are not. -j -- 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
