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.
Use INNER JOIN to join the tables.
Can you be a bit more specific?
Here's my SQL so far:
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.
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
but that's not the impression i got from post #1
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?
Yes, but then I thought that maybe an event could have a speaker OR a singer, and the other might be NULL. But I forgot to explain that in my answer.
page 35, chapter 3, "The FROM clause"
you will want to read the entire chapter
This topic is now archived. It is frozen and cannot be changed in any way.