On Wed, 1999-09-08 17:47:28 +0100, Clinton Gormley wrote:
> 3 Tables :
> 1) basket - shopping basket can contain one of two types of items :
> bottles or packs
> 2) bottle - contains a flag (extinct) marked 0 or 1
> 3) pack - contains a flag (extinct) marked 0 or 1
> Table - basket : comprises
> - basketID
> - clientID
> - bottleID } only one of these two will be non-zero, so each item
> - packID } is either a pack or a bottle
> I want to retrieve all rows in the basket which belong to a client, so :
> But I also want to check to make sure that the bottle or pack is not
> extinct (ie = 0)
> SELECT B.basketID,B.bottleID,B.packID
> basket B,
> bottle BT,
> pack P
> clientID = ?
> AND (
> (BT.extinct=0 AND B.bottleID = BT.bottleID)
> OR (P.extinct=0 AND B.packID = P.packID)
> Of course, this doesn't work. I get thousands of records back instead
> of 3! I think that what I'm trying to implement is a UNION. What
> should I be doing?
Hmm, change your table design ...?
But as a work-around: if I understood you correctly, basket.bottleID=0
for packs, and basket.packID=0 for bottles.
So you just need to insert dummy records with *ID=0 into the tables
bottle and pack, with an extinct value 0, and then can do this query:
SELECT B.basketID, B.bottleID, B.packID
clientID = ?
AND B.bottleID = BT.bottleID AND BT.extinct=0
AND B.packID = P.packID AND P.extinct=0
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26 E4 EC 80 58 7B 31 3A D7