Question 113566 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL query for multiple conditions

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,

Name Plan Status Subscription Date Cancel Date
Ajax 5-user Active Jan 2021 –
Zack 1-User Active Mar 2021 –
Ray 5-User Inactive Apr 2020 Dec 2020
Ray 1-User Active Dec 2020 –
Jack 1-user Inactive Nov 2020 Jun 2021
Kal 5-user Inactive Jan 2022 Feb 2022
Bruce 5-User Active Jan 2020 –
Kal 1-User Active Feb 2022 –
Diana 1-User Inactive Jul 2015 Jul 2020

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.

Tags:

3

Answers


  1. 0

    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"
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search