Self join on three columns with conditions

sai reddy

9y878SQL

------This is my query from a single table name called mis_subscription_details

SELECT DISTINCT DATE(created_timestamp) AS DATE,(

SELECT COUNT(*) FROM mis_subscription_details WHERE vastype='rrbt' AND DATE(created_timestamp)=DATE_SUB(CURDATE(), INTERVAL 6 DAY)

) AS Total_Subscription

(

SELECT COUNT(*) FROM mis_subscription_details WHERE vastype='RRBTChild' AND DATE(created_timestamp)=DATE_SUB(CURDATE(), INTERVAL 6 DAY)

) AS Total_Tone_Download_New

FROM mis_subscription_details WHERE DATE(created_timestamp)=DATE_SUB(CURDATE(), INTERVAL 7 DAY)

----From the above query i need three columns such as Date, Total_Subscription,Total_Tone_Download_New with joins

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
You have used inline queries which is kind of costly when it comes to running it for all the records. you can flatten this out with something like below ( from the function calls, it looks like you are using MySQL). Just to let you know that I tried this query in SQL server.
Something similar should work in your case.
SELECT DISTINCT
        created_timestamp,DATE(created_timestamp) AS DATE
INTO #tempDates
FROM mis_subscription_details
WHERE DATE(created_timestamp)=DATE_SUB(CURDATE(), INTERVAL 7 DAY)


SELECT DATE(#tempDates.created_timestamp) AS DATE,
                 CE.CNT AS Total_Subscription,
                 CE1.CNT AS Total_Tone_Download_New
FROM
#tempDates
INNER JOIN (

                SELECT  #tempDates.created_timestamp AS Date_RRBT, COUNT(*) AS CNT
                FROM mis_subscription_details
                INNER JOIN #tempDates ON mis_subscription_details.created_timestamp = #tempDates.created_timestamp
                WHERE vastype='rrbt'
                        AND DATE(mis_subscription_details.created_timestamp)=DATE_SUB(CURDATE(), INTERVAL 6 DAY)
                GROUP BY #tempDates.created_timestamp

) AS CE ON CE.Date_RRBT = #tempDates.created_timestamp
INNER JOIN (

                SELECT  #tempDates.created_timestamp AS Date_RRBTChild, COUNT(*) AS CNT
                FROM mis_subscription_details
                INNER JOIN #tempDates ON mis_subscription_details.created_timestamp = #tempDates.created_timestamp
                WHERE vastype='RRBTChild'
                        AND DATE(mis_subscription_details.created_timestamp)=DATE_SUB(CURDATE(), INTERVAL 6 DAY)
                GROUP BY #tempDates.created_timestamp

) AS CE1 ON CE1.Date_RRBTChild = #tempDates.created_timestamp
Guest User9y ago
Hi,

https://www.w3resource.com/sql/joins/using-a-where-cluase-to-join-three-or-more-tables-based-on-a-parent-child-relationship.php

Above link will guide you,How to make an Self join on three columns with conditions.
Zakir Ahamed9y ago
hi sai,

first you need to know why join is used in Query..

In Your Prblm Join is Unnecessary to use.
sai reddy9y ago
----Table
CREATE TABLE `mis_subscription_details` (
`msisdn` varchar(21) DEFAULT NULL,
`serviceid` varchar(45) DEFAULT NULL,
`shortcode` varchar(21) DEFAULT NULL,
`vendorid` varchar(11) DEFAULT NULL,
`mode` varchar(8) DEFAULT NULL,
`lang` char(10) DEFAULT NULL,
`reqtype` varchar(10) DEFAULT NULL,
`vastype` varchar(20) DEFAULT NULL,
`status` varchar(30) DEFAULT NULL,
`songid` varchar(30) DEFAULT NULL,
`songname` varchar(50) DEFAULT NULL,
`removal_songid` varchar(30) DEFAULT NULL,
`removal_songname` varchar(50) DEFAULT NULL,
`created_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`code` int(5) DEFAULT NULL,
`description` varchar(100) DEFAULT NULL,
`billedamount` int(5) DEFAULT NULL,
`validity` int(5) DEFAULT NULL
)

From the above table i have filtered query as follows.

SELECT DISTINCT DATE(created_timestamp) AS DATE,(
SELECT COUNT(*) FROM mis_subscription_details WHERE vastype='rrbt' AND DATE(created_timestamp)=DATE_SUB(CURDATE(), INTERVAL 6 DAY)
) AS Total_Subscription
,
(
SELECT COUNT(*) FROM mis_subscription_details WHERE vastype='RRBTChild' AND DATE(created_timestamp)=DATE_SUB(CURDATE(), INTERVAL 6 DAY)
) AS Total_Tone_Download_New
FROM mis_subscription_details WHERE DATE(created_timestamp)=DATE_SUB(CURDATE(), INTERVAL 7 DAY)
--------------------
I got the output well, but i need the same with joins.

I hope you will help me.
Midhun Tp9y ago
Hi,

It would be better if you can provide some sample data and desired output.