I’ve looked into a number forum posts and I can’t figure this out…
I have an SQL statement the returns the IDs as expected (spkr_id and singer_id). The IDs correspond to IDs in two separate (normalized?) tables (Speakers and Singers) that contains fields for the first and last names.
How do I go about having my SQL statement return the first and last names that correspond to the IDs?
I hope that’s enough information to explain what I’m looking for without having to copy table and SQL statement.
Here’s my SQL so far:
SELECT
curr_event.curr_title AS Title, curr_event.curr_date AS Date,
curr_event.curr_spkr_id AS SpeakerID,
si.singer_id AS SingerID
FROM curr_singers AS si
RIGHT OUTER JOIN curr_event ON si.sing_date = curr_event.curr_date
WHERE curr_event.curr_date >= now()
ORDER BY Date
As I stated earlier, I’m trying to get the first and last name of the curr_event.curr_spkr_id and si.singer_id from the speaker and singer tables, respectively.
SELECT
....
, si.singer_name
, sp.speaker_name
FROM Events
LEFT OUTER JOIN Singers AS si
ON events.singer_id = si.singer_id
LEFT OUTER JOIN Speakers AS sp
ON evenst.spkr_id = sp.spkr_id
guido, you were right the first time, use INNER JOIN
OUTER JOIN, whether LEFT or RIGHT, is incorrect
unless, of course, you can have a singer_id value in the events table that doesn’t exist in the singers table, or a speaker_id value in the events table that doesn’t exist in the speakers table
Thanks for the reply. I’ll try that solution using the INNER JOIN.
Hey I just realized that R937 is the author of the Simply SQL book! I have that book, but couldn’t find anything specific to my problem in it. Can you direct me to a page number that might explain how to solve my problem?