Richard,
This is the case for using a LEFT JOIN. You want everything from the "left"
table regardless of if it has a match in the "right" table. In this case
which table is left or right depends on which table name exists to the left
of the JOIN clause.
This will show you all events regardless if they are sponsored or not. If
an event has multiple sponsors, you will get one row for each event-sponsor
match. Events that do not have sponsors will return NULL for s.name .
SELECT FROM e.name, e.date, s.name
FROM event e
LEFT JOIN sponsor s
ON e.sponsor_ID = s.ID
In the other direction, if you wanted to see which sponsors didn't have
events yet:
SELECT FROM e.name, e.date, s.name
FROM event e
RIGHT JOIN sponsor s
ON e.sponsor_ID = s.ID
WHERE e.name IS NULL
Of course, with things being symmetric you could also write:
SELECT FROM e.name, e.date, s.name
FROM sponsor s
RIGHT JOIN event e
ON e.sponsor_ID = s.ID
and
SELECT FROM e.name, e.date, s.name
FROM sponsor s
LEFT JOIN event e
ON e.sponsor_ID = s.ID
WHERE e.name IS NULL
The LEFT or the RIGHT of a JOIN determines which table "drives" the join
and which table is "optional". You can mix and match as necessary. The rows
in the "optional" table that do not meet the condition(s) specified in the
ON clause will return NULL values for all columns for that row.
Shawn
<Richard Wrote>
I have two tables:
EVENT
ID name date sponsor_ID
23 Sady Hawkins 2004-11-04 235
89 Founders Day 2004-12-21 NULL
87 Winter Gala 2004-01-23 NULL
SPONSOR
ID name
235 George Suter
34 William Riggs
896 Lidia Bronson
I am having trouble writing a query that joins both tables but will pull
up an event even if it DOES NOT have a sponsor.
SELECT FROM e.name, e.date, s.name
FROM event e, sponsor s
WHERE e.sponsor_ID = s.ID
This query works only for those events that have a sposor.
Any help would be greatly appreciated.
Richard
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
