ricksj — 2012-06-19T02:40:26-04:00 — #1
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.
guido2004 — 2012-06-19T02:50:52-04:00 — #2
Use INNER JOIN to join the tables.
ricksj — 2012-06-19T09:17:38-04:00 — #3
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.
guido2004 — 2012-06-19T09:31:51-04:00 — #4
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
r937 — 2012-06-19T11:08:10-04:00 — #5
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
ricksj — 2012-06-19T11:27:10-04:00 — #6
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?
guido2004 — 2012-06-19T11:55:43-04:00 — #7
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.
r937 — 2012-06-19T12:59:15-04:00 — #8
page 35, chapter 3, "The FROM clause"
you will want to read the entire chapter
mittineague — 2014-09-22T20:08:44-04:00 — #9
This topic is now archived. It is frozen and cannot be changed in any way.