mardi 24 mars 2015

How to implement/improve/ make faster this query with joins

Please bear with me I am not skilled in SQL:


I have three tables


1) Notifications - stores all my data


2) GroupTable - Has the names of groups and related id


3) GroupUser - this table maps Uname and Udob to a group from GroupTable.


Now before I fetch records from Notifications I want to check the GroupTable for GroupID take this GroupID and look in GroupUser for all the records in this GroupID (Names,DOB as these are unique) Once I get this data I want to fetch records from Notifications table for the Names and DOB's in ascending order of the date:


So far I have the following query, it works fine just that I am not satisfied and I think this can be improved:



SELECT
*
FROM
(SELECT
*
FROM Notifications
WHERE
DateToNotify < '2016-03-24' AND
NotificationDateFor IN
(SELECT gu.Name
FROM GroupUser AS gu
INNER JOIN GroupTable AS gt ON
gu.GroupID = gt._id AND
gt.GroupName = "Groupn"
) AND
DOB IN
(SELECT gu.DOB
FROM GroupUser AS gu
INNER JOIN GroupTable AS gt ON
gu.GroupID = gt._id AND
gt.GroupName = "Groupn"
)
) as T

ORDER BY
SUBSTR(DATE('NOW'), 0) > SUBSTR(DateToNotify, 0)
, SUBSTR(DateToNotify, 0)

Aucun commentaire:

Enregistrer un commentaire