List:General Discussion« Previous MessageNext Message »
From:<skip Date:March 16 1999 2:56pm
Subject:How do I work around lack of UNION in MySQL?
View as plain text  
I have a music venue database with several fairly obvious tables.  The venue 
table contains a foreign key that references into a phone table.  I don't
always have phone information for a venue, however, so sometimes that key is 
0.  I'd like to select (for example), the venue name, city, state and phone, 
but am having trouble figuring out how to work around the partial missing
data.

From my background as a non-database but computer science person, it seems
logical to me that I would select the name, city and state for those venues
that have no phone field, and the name, city, state and phone for those that 
do, and create a union of the two (disjoint) sets:

    select venue.name, locale.city, locale.state
	from venue, address, locale
	where (venue.address = address.id)
	    and (address.locale = locale.id)
	    and (venue.phone = 0)
    UNION
    select venue.name, locale.city, locale.state, phone.voice
	from venue, address, locale, phone
	where (venue.address = address.id)
	    and (address.locale = locale.id)
	    and (venue.phone != 0)
	    and (venue.phone = phone.id)
    ;

or something like that.  That fails because MySQL doesn't support UNION.

I tried

    select venue.name, locale.city, locale.state, phone.voice
	from venue, address, locale, phone
	where (venue.address = address.id)
	    and (address.locale = locale.id)
	    and (venue.phone = 0 or venue.phone = phone.id)
    ;

but that seems to put the server into an infinite loop.  The simpler

    select venue.name, locale.city, locale.state
	from venue, address, locale
	where (venue.address = address.id)
	    and (address.locale = locale.id)
    ;

executes in the blink of an eye.

I'm obviously missing something.  How do I manage this?

Thx,

Skip Montanaro	| Mojam: "Uniting the World of Music" http://www.mojam.com/
skip@stripped  | Musi-Cal: http://www.musi-cal.com/
518-372-5583
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