MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Clinton Gormley Date:September 8 1999 4:47pm
Subject:Which JOIN's would make this work? (in place of UNION)
View as plain text  
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 :

SELECT B.basketID,B.bottleID,B.packID
FROM
	basket B
WHERE
	clientID = ?

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?

Many thanks

Clint

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