List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 8 1999 5:01pm
Subject:Re: Which JOIN's would make this work? (in place of UNION)
View as plain text  
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
> FROM
> 	basket B,
> 	bottle BT,
> 	pack P
> WHERE
> 	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
  FROM
          basket B,
          bottle BT,
          pack P
  WHERE
          clientID = ?
  AND     B.bottleID = BT.bottleID  AND  BT.extinct=0
  AND     B.packID = P.packID       AND  P.extinct=0
  ;

Regards,
  Martin
-- 
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
Thread
Which JOIN's would make this work? (in place of UNION)Clinton Gormley8 Sep
  • Re: Which JOIN's would make this work? (in place of UNION)Martin Ramsch8 Sep