Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.