CODEWITHSUNDEEP
mysqlsql
Petr B
Petr B

Reputation: 529

Are two selects needed?

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?

Upvotes: 3

Views: 60

Answers (3)

LSerni
LSerni

Reputation: 57388

You need two different sets of information (From's user profile, and To's user profile) depending on the direction of the visit.

So you can:

do a double-JOIN combined with a lot of IF's

  SELECT IF ( FromId = f.uid, f.name, t.name ) AS name,
       IF ( FromId = f.uid, f.age,  t.age  ) AS age,
       ...
  FROM Visit
    JOIN UserProfile AS f ON (uid = FromId)
    JOIN UserProfile AS t ON (uid = ToId)
  ORDER BY VisitTime

do the same, renaming fields, selecting all and doing the choice in the code on which set of fields you'll extract from the tuple (tuple is now double in size)

SELECT f.name as f_name, t.name AS t_name, ...

use a UNION. That's two queries rolled in one, and then you need a Sort

SELECT * FROM (
    SELECT UserProfile.*, 'From' AS direction
        FROM UserProfile JOIN Visit ON (FromId = uid)
    UNION
    SELECT UserProfile.*, 'To' AS direction
        FROM UserProfile JOIN Visit ON (ToId = uid)
) AS visits ORDER BY VisitTime

I believe this third option to be probably simpler. The first may yield better performances, or not, depending on indexing and actual table structure and size. For tables of a few hundred or thousand visits, the difference is probably negligible.

Upvotes: 1

John Woo
John Woo

Reputation: 263713

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.

Upvotes: 5

Mario
Mario

Reputation: 36487

You can just expand your WHERE clause to include a second condition using OR:

SELECT * FROM Visit WHERE FromId = uid OR ToId = uid;

This will get you all visits, but if you want all userprofiles, just extend the SELECT statement:

SELECT * FROM UserProfile, Visit WHERE FromId = uid OR ToId = uid;

To sort the results, just add ORDER BYat the end:

SELECT * FROM UserProfile, Visit WHERE FromId = uid OR ToId = uid SORT BY VisitTime DESC;

Upvotes: 0

Related Questions