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
