<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
