From: Michael Widenius Date: March 16 1999 6:59pm Subject: How do I work around lack of UNION in MySQL? List-Archive: http://lists.mysql.com/mysql/348 Message-Id: <14062.43453.491568.524246@monty.pp.sci.fi> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit >>>>> "skip" == skip writes: skip> I have a music venue database with several fairly obvious tables. The venue skip> table contains a foreign key that references into a phone table. I don't skip> always have phone information for a venue, however, so sometimes that key is skip> 0. I'd like to select (for example), the venue name, city, state and phone, skip> but am having trouble figuring out how to work around the partial missing skip> data. >> From my background as a non-database but computer science person, it seems skip> logical to me that I would select the name, city and state for those venues skip> that have no phone field, and the name, city, state and phone for those that skip> do, and create a union of the two (disjoint) sets: skip> I tried skip> select venue.name, locale.city, locale.state, phone.voice skip> from venue, address, locale, phone skip> where (venue.address = address.id) skip> and (address.locale = locale.id) skip> and (venue.phone = 0 or venue.phone = phone.id) skip> ; skip> but that seems to put the server into an infinite loop. The above should work, but it will be a bit slower than without the or test, as MySQL can't use a direct key on phone. Check your query with EXPLAIN and try adding a key on 'venue.address'. Regards, Monty