CODEWITHSUNDEEP
mysqlsql
Shriram
Shriram

Reputation: 3

Fetch name based on single query

Need help with below query which I am not able to figure out I have two tables

Table 1 - User

UserId  Name
------  ----
1       Abc
2       Xyz
3       Pqr
4       Ppp
5       AAA

Table 2 - SubscriptionAssignment

UserId  PlanName  PurchasedBy
------  --------  -----------
1       Basic     3
2       Pro       3
4       Pro       5

(UserId and PurchasedBy has foreign key reference to UserId in User table). I want to get reporting data by joining these two table in below format where lets say

PurchasedBy=3

Name  PlanName  PurchasedBy
----  --------  -----------
Abc   Basic     Pqr
Xyz   Pro       Pqr

Upvotes: 0

Views: 93

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

You just need to do join the two tables, along with a second join back to User to bring in the names of the users who did the purchasing.

SELECT
    u1.Name,
    sa.PlanName,
    u2.Name AS PurchasedBy
FROM User u1
INNER JOIN SubscriptionAssignment sa
    ON sa.UserId = u1.UserId
INNER JOIN User u2
    ON u2.UserId = sa.PurchasedBy
WHERE
    sa.PurchasedBy = 3;

screen capture from demo link below

Demo

Upvotes: 1

Related Questions