Possible alternatives to a bunch of sub Queries

Question

I am querying on date column in table 1 based on subqueries. Subquery 1 in where clause gets description from foreign key referred table and subquery 2 pulls in data by some other condition.

So my question is, what alternative I have to make this query better?

SELECT count(*) AS non_saturday
FROM t_booking
WHERE p_id IN (
        SELECT p_id
        FROM p_detail
        WHERE p_name IN ('A','B')
        )
    AND date_format(p_date, '%Y%m%d') IN (
        SELECT DISTINCT CONCAT (
                gregorian_ccyy
                ,gregorian_month
                ,gregorian_day
                )
        FROM m_gregorian_calendar1 c
        WHERE c.day_of_the_week = (
                SELECT day_id
                FROM m_gregorian_day
                WHERE day_name = 'SATURDAY'
                )
            AND c.gregorian_ccyy = '2012'
            AND c.gregorian_month = '10'
        )

One suggestion would be to look at using JOIN instead of the subqueries. — Taryn, Jul 30 '13 at 16:34
As @bluefeet mentioned it appears you can simply join your three tables rather than use subqueries. You would however not want to use that date_format/concatenation business for joining the tables, as this would not allow use of index. — Mike Brant, Jul 30 '13 at 16:39
I forgot to mention my adding the year 2012 and month 10 was hardcoded as this is coming from a form. As suggested, I could use an inner join but I think it will make the query less readable. — yoda, Jul 30 '13 at 16:44
Anyone who is not able to read join syntax is not qualified for work that involves writing queries. — Dan Bracuk, Jul 30 '13 at 16:46

score 2 · Accepted Answer · edited May 23 '17 at 12:14

2

This is not maybe a straight answer to your question, but if by "Saturday" you mean the straight Saturday (as opposed to some calendar with different mappings between the date and the days of the week), it seems to me that you could simply run

SELECT count(*) AS non_saturday
    FROM t_booking
    JOIN p_detail ON (t_booking.p_id = p_detail.p_id AND p_name IN ('A','B'))
    WHERE
        DAYOFWEEK(p_date) = 7
        AND MONTH(p_date) = 10
        AND YEAR (p_date) = 2012

or to better use indexing on the p_date field,

    WHERE p_date BETWEEN "2012-10-01" AND "2012-10-31"

(but on the use of BETWEEN in date fields, check also this answer).

edited May 23 '17 at 12:14

Community

1
1

answered Jul 30 '13 at 16:48

LSerni

55,617
10
65
107

This is awesome. Did not know about the dayofweek function. I can use it directly here instead of filtering from calendar table which has day_of_week column along the lines of the function. One thing I did not understand is why the and condition in on clause? – yoda Jul 30 '13 at 17:04
That's because the `p_detail` table has a `WHERE` condition based on `p_name`, so I can't make a straight `JOIN` and must add the equivalent of the `WHERE`. You might want to check out some tutorial on the use of `JOIN`s (and maybe indexes too). – LSerni Jul 30 '13 at 23:13

Possible alternatives to a bunch of sub Queries

1 Answers1