Re: Query help

[Richard Reina]

Thank you very much Shawn and Mike for your quick responses.  Left join 
was exactly what I was looking for and it worked quite nicely.

Once again, thanks for your help.

Richard

[EMAIL PROTECTED] wrote:

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]