List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 16 1999 6:59pm
Subject:How do I work around lack of UNION in MySQL?
View as plain text  
<cut>

>>>>> "skip" == skip  <skip@stripped> 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:

<cut>

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


Thread
How do I work around lack of UNION in MySQL?skip16 Mar
  • Re: How do I work around lack of UNION in MySQL?Thimble Smith16 Mar
  • How do I work around lack of UNION in MySQL?Michael Widenius16 Mar
    • open_files limit?Brandon Pulsipher16 Mar
      • open_files limit?Michael Widenius16 Mar
      • Re: open_files limit?Matthias Pigulla17 Mar
RE: open_files limit?Robin Bowes17 Mar