From: Robin Bowes
Date: March 16 1999 3:19pm
Subject: RE: How do I work around lack of UNION i
List-Archive: http://lists.mysql.com/mysql/331
Message-Id: <164215189.686676949.98@batmobile.eoc.org.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>
> 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.

Use a left-join.

eg.

      select venue.name, locale.city, locale.state
 	from venue
	join address on venue.address = address.id
	join locale on address.locale = locale.id
	left join phone on venue.phone = phone.id

(this is off the top of my head so the syntax my be slightly wrong)

R.
 --
Robin Bowes, System Development Manager, Equal Opportunities Commission,
Room 405A, Overseas House, Quay St., Manchester, M3 3HN, UK.
Tel: +44 (0) 161 838 8321  Fax: +44 (0) 161 835 1657

Lord, grant me the serenity to accept the things I cannot change,
the courage to change the things I can, and the wisdom to hide the
bodies of the people I had to kill because they pissed me off  - Anon.