CODEWITHSUNDEEP
mysql
neeks
neeks

Reputation: 312

MYSQL how to retrieve relevant values of two foreign keys

I have a master table of users with some basic structure as

Users

userId | userName | userJoinedOn

159 | Alex | 2015-08-23

160 | Steve | 2015-08-23

161 | Rocky | 2015-08-23

A user transactions table is as follows. Here user1 and user2 are foreign keys to users table.

UserTransactions

user1 | user2 | transaction

159 | 160 | 3

159 | 161 | 2

160 | 159 | 2

160 | 161 | 2

161 | 159 | 2

161 | 160 | 2

I want to retrieve the above transaction as follows:

user1Id | user1Name | user2Id | user2Name | transaction

159 | Alex | 160 | Steve | 3

Also, can it be further narrowed down to two users who have performed the same transaction with each other. Like 160 and 161 have both done transaction 2 with each other.

I am assuming this is going to be pretty resource intensive for the servers with large number of users. Please suggest.

Upvotes: 0

Views: 32

Answers (3)

Valery Viktorovsky
Valery Viktorovsky

Reputation: 6726

Just join 2 tables:

SELECT
    u1.userId as user1Id,
    u1.userName as user1Name,
    u2.userId as user2Id,
    u2.userName as user2Name,
    ut.transaction
FROM
    UserTransactions ut
        INNER JOIN Users u1 ON (ut.user1 = u1.userId)
        INNER JOIN Users u2 ON (ut.user2 = u2.userId)

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

The most common type of join is SQL INNER JOIN. An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

user1 column in the UserTransactions table refers to the userId in the Users table. user2 column in the UserTransactions table refers to the userId in the Users table. So we must join UserTransactions with Users table twice.

Upvotes: 1

Jens
Jens

Reputation: 69440

Join the users table twice with alias:

select user1.userId , user1.username ,user2.userId , user2.username, transaction
from usertransaction join users user1 on user1=users.userid
join users user2 on user2=users.userid

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You need two joins:

select ut.User1Id, u1.userName, ut.User2Id, u2.userName, ut.transaction
from UserTransactions ut left join
     Users u1
     on ut.user1Id = u1.userid left join
     Users u2
     on ut.user2Id = u2.userid;

Upvotes: 0

Related Questions