Convert ID to Full Name in SQL statement

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:
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

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?

Thanks!

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

:slight_smile: