Getting related data from an uncertain column

Question

I am working on a friendship logic, the table friendship has this structure:

`friendship_id` INT NOT NULL ,
  `user1_id` INT NOT NULL ,
  `user2_id` INT NOT NULL ,
  `status` INT NULL ,
  `created` DATETIME NULL ,
  `message` VARCHAR(255) NULL ,

And the profile table.

`profile_id` INT NOT NULL ,
  `user_id` INT NOT NULL ,
  `first_name` VARCHAR(255) NULL ,
  `last_name` VARCHAR(255) NULL ,
  `image_url` VARCHAR(255) NULL ,
  `gender` INT NULL ,
  `city_id` INT NULL ,
  `country_id` INT NULL ,

I am trying to get a list of the the friends of a certain user, but I want related info also, for example their city.

The problem is the user_id could be either in user1_id or user2_id.

I can try with a left join but the results are not ok:

select
        city.name,
            p1.first_name as 'user1_first_name',
            p1.last_name as 'user1_last_name',
                p1.user_id as 'user1_id',
                p1.profile_id as 'user1_profile_id',
                p1.image_url as 'user1_image',
                p2.first_name as 'user2_first_name',
                p2.last_name as 'user2_last_name',
                p2.user_id as 'user2_id',
                p2.profile_id as 'user2_profile_id',
                p2.image_url as 'user2_image'
            from friendship f
            left join profile p1 on p1.user_id = user1_id
            left join profile p2 on p2.user_id = user2_id
            left join city on p1.user_id = 1 and city.city_id = p1.city_id or city.city_id = p2.city_id
            where f.user1_id = 1 or f.user2_id = 1 and f.status = 2

Note: Probably my sql is not the best thing you'll ever see :)

What's the best way to get the information?

Answer 1

It looks like some of your difficulty may be order of operations related. For example, your

where f.user1_id = 1 or f.user2_id = 1 and f.status = 2

is evaluated as

where f.user1_id = 1 or (f.user2_id = 1 and f.status = 2)

It may be more straightforward to start from the profile table of the user you're starting from :

from [profile] p1
left join city c
    on p1.city_id = c.city_id
left join friendship f
    on p1.[user_id] in (f.user1_id,f.user2_id)
    and f.[status] = 2
left join [profile] p2
    on p2.[user_id] in (f.user1_id,f.user2_id)
    and p1.[user_id] <> p2.[user_id]
where p1.[user_id] = 1