Are two selects needed?

Question

I have a table:

Visit (FromId, ToId, VisitTime)

where FromId and ToId are FKs to table

UserProfile (uid, name, age ...)

As a user with my UID I want to select all profiles I have visited or who visited me in one result set ordered by VisitTime and with the indication of the "direction of the visit".

Is it possible to do it using only one MySQL query?

Answer 1

SELECT  CASE WHEN a.FromID = 'yourIDHere' 
                THEN c.Name
                ELSE b.Name
        END Name,
        CASE WHEN a.FromID = 'yourIDHere' 
                THEN c.Age
                ELSE b.Age
        END Age,
        a.VisitTime,
        CASE WHEN a.FromID = 'yourIDHere' 
                THEN 'You'
                ELSE 'Friend'
        END DirectionOfVisit
FROM    Visit a
        INNER JOIN UserProfile b
            ON a.FromID = b.Uid
        INNER JOIN UserProfile c
            ON a.ToID = c.Uid
WHERE   'yourIDHere' IN (a.FromID, a.ToID)
ORDER   BY a.VisitTime

Brief Explanation:

The query will display the name of your friend you visited or who have visited you and will also display the direction of the visit. When it displays You, it means that you have visited your friend's profile, otherwise it will display Friend if the friend have visited you.

• SQLFiddle Demo