INNER JOIN Statement

Question

I know this is a simple INNER JOIN statement, I just can't get the syntax correct.

The scenario is this, Table 1 (AllUserData) contains the INT7 and INT6 fields. INT7 is basically "Requested by" and INT6 is "Requested for". These 2 fields contain an ID. Table 2 (UserInfo) has the field tp_ID which hold their ID and tp_Title is their name.

What I currently have is:

    SELECT alluserdata.int7, alluserdata.int6, UserInfo.tp_ID, 
           userinfo.tp_Title
    FROM alluserdata
    INNER JOIN UserInfo on AllUserData.int7 = UserInfo.tp_ID or 
                           AllUserData.int6 = UserInfo.tp_ID

The problem with that is, the output will be on two lines. One line will show the "Requested by" name (int7) and one line will show the "Requested for" name (int6). I want these entries to be on the same line.

Answer 1

You need to JOIN twice:

SELECT a.int7, a.int6,
       u1.tp_ID, u1.tp_Title,
       u2.tp_ID, u2.tp_Title
FROM alluserdata a
LEFT JOIN UserInfo u1 on a.int7 = u1.tp_ID 
LEFT JOIN UserInfo u2 ON a.int6 = u2.tp_ID
WHERE u1.tp_ID is not null or u2.tp_ID is not null;