* Dee
> I have a table that contains ids for several other tables. Setup is as
> followed:
>
> +--------------+------------------+------+-----+---------+--------
> --------+
> | Field | Type | Null | Key | Default | Extra
> |
> +--------------+------------------+------+-----+---------+--------
> --------+
> | id | int(11) | | PRI | NULL |auto_increment |
> | title | varchar(100) | YES | | NULL |
> |
> | rateID | int(10) unsigned | YES | | NULL |
> |
> | actorID | int(10) unsigned | YES | | NULL |
> |
> | actor2ID | int(10) unsigned | YES | | NULL |
> |
> | actor3ID | int(10) unsigned | YES | | NULL |
> |
> +--------------+------------------+------+-----+---------+--------
> --------+
>
> +---------+------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +---------+------------------+------+-----+---------+----------------+
> | actorID | int(10) unsigned | | PRI | NULL | auto_increment |
> | first | varchar(20) | YES | | NULL | |
> | middle | varchar(20) | YES | | NULL | |
> | last | varchar(20) | YES | | NULL | |
> +---------+------------------+------+-----+---------+----------------+
>
> --------+------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +--------+------------------+------+-----+---------+----------------+
> | rateID | int(10) unsigned | | PRI | NULL | auto_increment |
> | rating | varchar(15) | YES | | NULL | |
> +--------+------------------+------+-----+---------+----------------+
>
>
> How can I query the movie tables so that it will get the names of the
> actors when each actor field may have a different name or may be null?
>
> I am unable to query on more than one actor.
Two things to remember:
- When you need to join the same table multiple times in the same query, you
have to use aliases for the table name.
- When there could be no row found for a join, but you still want a result
row, you need to use LEFT JOIN.
Try this:
SELECT title,a1.last,a2.last,a3.last
FROM movie
LEFT JOIN actor a1 ON
a1.actorID = movie.actorId
LEFT JOIN actor a2 ON
a2.actorID = movie.actor2Id
LEFT JOIN actor a3 ON
a3.actorID = movie.actor3Id
HTH,
--
Roger
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
