SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Aug 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Use INNER JOIN to join the tables.

  3. #3
    SitePoint Member
    Join Date
    Aug 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Aug 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ricksj View Post
    Can you direct me to a page number that might explain how to solve my problem?
    page 35, chapter 3, "The FROM clause"

    you will want to read the entire chapter

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •