From: <skip
Date: March 16 1999 2:56pm
Subject: How do I work around lack of UNION in MySQL?
List-Archive: http://lists.mysql.com/mysql/328
Message-Id: <199903161456.JAA02390@dolphin.calendar.com>
MIME-Version: 1.0 (generated by tm-edit 7.108)
Content-Type: text/plain; charset=US-ASCII


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