CODEWITHSUNDEEP
mysqljoin
Nikki Mather
Nikki Mather

Reputation: 1148

MySQL inner join on differently named columns

I'm trying to make a friends list functionality on a website i'm creating purely for my own education into learning PHP. I have a table called friends and it has the column names id1, id2, status1, status2 - the ID of the person who has added a friend is in the id1 column, whilst the ID of the person who was added is in the id2 column. As for the status1, this holds a value 0 or 1 - it is set to 1 by default, when a friend is added - for status2, this is set to 0 by default and gets set to 1 when a the person who was added confirms the friends request.

Now, what i need to do is join two tables so i can ascertain the usernames of the person who was added and the person who made the request. The id1 and id2 relate to the id column in another table called user_info.

Now i believe i need to do an inner join on friends and user_info, however i only know how to do that when the columns share the same name. How do i do an innerjoin on two differently named columns?

Upvotes: 2

Views: 102

Answers (2)

musa
musa

Reputation: 1465

Maybe kinda off-topic but I want to answer your question with suggesting an other schema.

Check this out; http://sqlfiddle.com/#!2/7dde0/4

create table users (
  id int,
  name varchar(255)
);

create table friendships (
  user1 int,
  user2 int
);

create table friend_requests (
  user1 int,
  user2 int
);

# We have 3 users
insert into users values(1, 'John'), (2, 'Alice'), (3, 'Tom');

# John and Alice are friends
insert into friendships values(1, 2);

# Alice and Tom are friends
insert into friendships values(2, 3);

# John send Tom a friend request
insert into friend_requests values(1, 3);

# John's friends
select id, name from users
inner join friendships
on user2 = id and user1 = 1
or user1 = id and user2 = 1;

# Tom's friend requests
select id, name from users
inner join friend_requests on id = user1
where user2 = 3;

# Alice's friends
select id, name from users
inner join friendships
on user2 = id and user1 = 2
or user1 = id and user2 = 2;

Upvotes: 2

jiy
jiy

Reputation: 868

This has been tested and seems to work.

With this user_info table:

enter image description here

And with this friends table:

enter image description here

Running this query:

select ui1.name as initiator, ui2.name as responder
from pl2.friends f
left join pl2.user_info ui1 on f.id1 = ui1.id
left join pl2.user_info ui2 on f.id2 = ui2.id

You get:

enter image description here

Upvotes: 0

Related Questions