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?