How to join 2 tables with getting all rows from left table and only matching ones in right

Table 1

enter image description here

Table 2

enter image description here

Table2.plan_selected shows what plan did the user choose.

Eg: The user with user_id=4 in Table2 choose the id = 2 plan from Table1.

I want to get all the rows from Table1 and only matching rows from Table2 for a particular user_id.

The expected result is like this.

I want to fetch all the rows of Table1 and only the selected plan from Table2 for a particular user_id lets say 4.

The expected result will be like this:

id name plantype plandetails requestpermonth price isdeleted planselected

1 EXECUTIVE MONTHLY {1000 MAY REQUSTS} 1000 50 0 NULL

2 BASIC MONTHLY {500 MAY REQUSTS} 1000 25 0 2

3 FREEEE MONTHLY {10 MAY REQUSTS} 1000 0 0 NULL

4 EXECUTIVE YEARLY {1000 MAY REQUSTS} 1000 500 0 NULL

5 BASIC YEARLY {500 MAY REQUSTS} 1000 250 0 NULL

6 FREEEE YEARLY {10 MAY REQUSTS} 1000 0 0 NULL

What I have tried to do was use a simple left join.

select plans.id, name, plan_details, plan_type, request_per_month, price,is_deleted, plan_selected from SubscriptionsPlans as plans left join SubscriptionsOrder as orders on plans.id=orders.plan_selected where orders.user_id = 4

These are my 2 models. ORM queryset or SQL query will help

class SubscriptionsPlans(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=255)
    plan_type = models.CharField(max_length=255)
    plan_details = models.TextField(max_length=1000)
    request_per_month = models.IntegerField()
    price = models.FloatField()
    is_deleted = models.BooleanField(default=False)

class SubscriptionsOrder(models.Model):
    id = models.IntegerField(primary_key=True)
    user_id = models.ForeignKey(
        AppUser,
        null=True,
        on_delete=models.SET_NULL
    )
    plan_selected = models.ForeignKey(SubscriptionsPlans, null=True, on_delete=models.SET_NULL)
    billing_info = models.IntegerField()

You can query with:

SubscriptionsPlans.objects.filter(subscriptionsorder__user_id=4)

This will list all SubscriptionPlans for which there is a SubscriptionOrder with 4 as user_id.

Back to Top