Thanks Chuck! The last example you gave looked just right, but when I ran it, it returned an error. A quick look at the mysql manual revealed that UNION SELECTs are on the TO DO list for mysql :(
With the examples you provided though, I was able to create the following statement which returns exactly what I wanted. select distinct X.id, X.title from X, Y where X.id=Z or (X.id=Y.assoc and Y.id=Z); when Z = 1 +----+-------+ | id | title | +----+-------+ | 1 | one | | 3 | three | +----+-------+ when Z = 2 +----+-------+ | id | title | +----+-------+ | 2 | two | | 3 | three | | 4 | four | +----+-------+ and when Z = 3 or 4 or 5, it just returns the single row that I would expect. Again, thanks. Carl > > Carl Franks wrote: > Hi, > > I have 2 tables, "X" and "Y". > > table X table Y > +----+-------+ +----+-------+ > | id | title | | id | assoc | > +----+-------+ +----+-------+ > | 1 | one | | 1 | 3 | > | 2 | two | | 2 | 3 | > | 3 | three | | 2 | 4 | > | 4 | four | +----+-------+ > | 5 | five | > +----+-------+ > > I wish to retrieve the X.id and X.title where X.id=Z > select X.id, X.title from X where X.id = Z > and also the X.id and X.title where (X.id=Y.assoc) when (Y.id=X.id) > select X.id, X.title from X, Y where X.id = Y.assoc and X.id = Y.id > So... I think you want the OR of those two statements. The obvious > way to do this might be: > select X.id, X.title from X where X.id = Z > union > select X.id, X.title from X, Y where X.id = Y.assoc and X.id = Y.id > or: > select X.id, X.title from X, Y > where ( X.id = Z and not exists (select * from X, Y where X.id = > Y.assoc and X.id = Y.id) > or X.id = Y.assoc and X.id = Y.id) > The above won't work in current versions of mysql, and probably can't make > very good use of indices. > It can be simulated in mysql via: > select distinct X.id, X.title from X, Y > where ( X.id = Z > or X.id = Y.assoc and X.id = Y.id) > > but I'm trouble trying to express this as a single SQL statement. > Could someone help me out with this, or at least give me a clue as to how to > think this one through? > > In case I haven't explained it very well, I'll describe exactly what I'm > doing. > Z is a product number, and I want to retrieve the (table X) data for product > Z and also the (table X) data for any other products associated with it, > represented by table Y. > The above paragraph seems different than what you said above. To get > the data for products associated with product Z, I think you want > > Y.id = Z and X.id = Y.assoc > > select X.id, X.title from X where X.id = Z > union > select X.id, X.title from X, Y where X.id = Y.assoc and Y.id = Z > > Chuck > > > I know I could do it using 2 select statements, firstly just selecting > X.id, X.title, Y.id, Y.assoc where X.id=Z > (I'm using perl DBI) > and then within the perl program create another SQL statement selecting > X.id, X.title WHERE Y.assoc IS NOT NULL in the results from the first > select. > However, I would like to do this with one SELECT if it's possible, for > efficiency. > > Thanks, > Carl > > > > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > <mailto:[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > <mailto:[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
