SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT Query with two foreign keys to one table

    Okay, here's my newest problem:

    So I've got a table, it has an ID to a User and to a Manager, both of which are stored in a seperate table, but both are in the same table (basically, managers are also users). In my view, I do an INNER JOIN on the Users table for both the User and Manager, but it pulls the same records out for both. Basically, my join looks like:

    INNER JOIN u.USER_ID = et.ENG_ID OR u.USER_ID = er.ENG_MAN
    u is my users table, et is the table with the user id and er is the table with the manager id. I know there's a way to do this, but I can't remember how. Any ideas?

    Thanks,
    Joe Fiorini
    -Joe

  2. #2
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Never mind...

    Never mind, I figured it out. Damn, I love being me.

    -Joe
    -Joe

  3. #3
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please post the answer you found so that other users with the same question can find the answer - otherwise they'll post the same question again, and the cycle continues......!


    M@rco

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Oops...

    Uhh, yeah, I knew that... uhhh...:P

    I realized that I could create multiple instances of a table and select the value from the new instance. For example:

    SELECT U.Username, U2.Username AS Manager FROM PAYCHECKS P INNER JOIN Users U ON U.UID = P.UID INNER JOIN Users U2 ON U2.UID = P.UID
    This creates two instances of the Users table (U and U2) and only grabs users that exist in the users table and only the managers in the users table.

    Here is an interesting thing I learned the other day from somebody in this forum. If you are using a repository database for your users (for the sake of example, call it UserDB), you could also do this:

    SELECT U.Username, U2.Username AS Manager FROM PAYCHECKS P INNER JOIN UserDB.dbo.Users U ON U.UID = P.UID INNER JOIN UserDB.dbo.Users U2 ON U2.UID = P.UID
    Also, if you don't know the owner you could do this:

    SELECT U.Username, U2.Username AS Manager FROM PAYCHECKS P INNER JOIN UserDB..Users U ON U.UID = P.UID INNER JOIN UserDB..Users U2 ON U2.UID = P.UID
    Okay, that's all. Time for me to get to work.

    Later all,
    Joe Fiorini
    -Joe


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
  •