I have a table of users who have subscribed to different plans of a streaming services and other data about the said users. Something similar to this,

I need to write a sql query to find out the list of users who have cancelled the 5 user plan and bought the 1-user plan. Names - WHERE Plan=5-user and status= Inactive Plan = 1-user and Status=Active. Also, the result should displav the names with the current active plan.(would help if all the other columns in the table are displayed along with it too)

I tried

SELECT NAME
FROM Table
WHERE Plan = 5-user AND Status = Inactive
OR Plan = 1-User AND Status = Active

EXPECTED RESULT : |Ray|1-User|Dec 2020 |Kal|1-User|Feb 2022

P.S I'm new to this site and I have just started learning SQL, I apologise in advance for any error in my question framing.

CodePudding user response：

You may use the EXISTS operator as the following:

SELECT Name, Plan, Status, 
       DATE_FORMAT(SubscriptionDate,'%M %Y') SubscriptionDate
FROM table_name T
WHERE EXISTS (SELECT 1 FROM table_name D 
              WHERE D.Name=T.Name AND
                    D.Plan='5-User' AND D.Status='Inactive' AND
                    D.SubscriptionDate<T.SubscriptionDate
             )
AND Plan='1-User' AND Status ='Active'
                

See a demo.

CodePudding user response：

We can use INTERSECT to perform an intersection between the two types of people, namely ones who have purchased [Import, Apple] and [Native, Orange].

SELECT 
    "name"
FROM
    people
WHERE
    people.fruit = "Apple"
    AND
    people.type = "Import"
INTERSECT
SELECT 
    "name"
FROM
    people
WHERE
    people.fruit = "Orange"
    AND
    people.type = "Native"

CodePudding user response：

In this case I suggest you to use UNION operator

SELECT *
FROM Table
WHERE Plan = 5-user AND Status = Inactive

UNION

SELECT *
FROM Table
OR Plan = 1-User AND Status = Active