You should be able to do what you want with a single SELECT.
You are on the right track with trying to use UNION and EXCEPT. What you are
trying to do can be accomplished with a generalization of an outer join.
Try something like this pseudo-code, which uses common-table-expressions for
readability and to cut redundancy:
with
MatchA is (select TableA.* from TableA.* inner join TableB),
NomatchA is (TableA except MatchA)
(select * from MatchA inner join TableB)
union
(select * from NonmatchA inner join TableC)
... assuming TableC has the values you want to use instead of those from TableB
for TableA records that don't match.
Or alter the above query to taste for the semantics you actually want.
-- Darren Duncan
Peter Haworth wrote:
> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
