Is there a way to condense a few different queries (almost identical) into one?

Question

I have about 4 or 5 different queries that are all exactly like with the exception of what is contained in the WHERE clause. For example, these are reports that operate by date so one report uses a date of Now() and the other reports use dates by month and 15 days. All of the fields are exactly the same. Is there a way to combine these into a single query and just change the data in the WHERE clause on the fly? Or some other solution??

Thanks

---

OK Guys, thanks for the replies. Here are the actual queries. Please notice that the where clause is the only thing that differs and I really hate having to write another query that is almost exactly alike whenever I need to add a new report.

SELECT t1.customer_id, t1.name, t2.dtime, t6.band_type, t7.dop, t5.lun, t2.rpt
FROM
customer AS t1
Inner Join report AS t2 ON t1.customer_id = t2.customer_id
Inner Join employee AS t5 ON t5.employee_id = t2.employee_id
Inner Join band_type AS t6 ON t6.band_type_id = t2.band_type_id
Inner Join dop AS t7 ON t7.dop_id = t2.dop_id
WHERE
t2.rpt_type = 'daily' AND
t2.tmstamp >= date_sub(curdate(), interval 1 month) AND
t2.tmstamp <= curdate() AND
t1.customer_id = ''



WHERE
t2.tmstamp >= '$date 00:00:00' AND
t2.tmstamp <= '$date 23:59:59' AND
t1.customer_id = '' AND
t2.deleted =  '0'


WHERE
t2.stamp >= date_sub(now(), interval 1 day) AND
t1.customer_id = '' AND
t2.band_type = '' AND
t2.deleted = '0'

Answer 1

Use OR or UNION ALL:

SELECT  *
FROM    mytable
WHERE   date = NOW() OR date = NOW() - INTERVAL 1 DAY

SELECT  *
FROM    mytable
WHERE   date = NOW()
UNION ALL
SELECT  *
FROM    mytable
WHERE   date = NOW() - INTERVAL 1 DAY

Update:

If you want three resultsets, you'll need three queries, and if you have three queries, you have three SELECT lists.

You can compose your queries within the application (you create SELECT list once and just add a WHERE condition).

Alternatively, you can get everything in a single query and parse the results on the client:

SELECT 1 AS resultset,
       t1.customer_id, t1.name, t2.dtime, t6.band_type, t7.dop, t5.lun, t2.rpt
FROM
customer AS t1
Inner Join report AS t2 ON t1.customer_id = t2.customer_id
Inner Join employee AS t5 ON t5.employee_id = t2.employee_id
Inner Join band_type AS t6 ON t6.band_type_id = t2.band_type_id
Inner Join dop AS t7 ON t7.dop_id = t2.dop_id
WHERE
t2.rpt_type = 'daily' AND
t2.tmstamp >= date_sub(curdate(), interval 1 month) AND
t2.tmstamp <= curdate() AND
t1.customer_id = ''
UNION ALL
SELECT 2 AS resultset,
       t1.customer_id, t1.name, t2.dtime, t6.band_type, t7.dop, t5.lun, t2.rpt
FROM
customer AS t1
Inner Join report AS t2 ON t1.customer_id = t2.customer_id
Inner Join employee AS t5 ON t5.employee_id = t2.employee_id
Inner Join band_type AS t6 ON t6.band_type_id = t2.band_type_id
Inner Join dop AS t7 ON t7.dop_id = t2.dop_id
WHERE
t2.tmstamp >= '$date 00:00:00' AND
t2.tmstamp <= '$date 23:59:59' AND
t1.customer_id = '' AND
t2.deleted =  '0'
SELECT 3 AS resultset,
       t1.customer_id, t1.name, t2.dtime, t6.band_type, t7.dop, t5.lun, t2.rpt
FROM
customer AS t1
Inner Join report AS t2 ON t1.customer_id = t2.customer_id
Inner Join employee AS t5 ON t5.employee_id = t2.employee_id
Inner Join band_type AS t6 ON t6.band_type_id = t2.band_type_id
Inner Join dop AS t7 ON t7.dop_id = t2.dop_id
WHERE
t2.stamp >= date_sub(now(), interval 1 day) AND
t1.customer_id = '' AND
t2.band_type = '' AND
t2.deleted = '0'

Here, all 3 resultsets are returned as a single resultset, with an additional field (resultset) that allows to distinguish between them.

Of course you can also create a view like this:

CREATE VIEW v_customers AS
SELECT t1.customer_id, t1.name, t2.dtime, t6.band_type, t7.dop, t5.lun, t2.rpt
FROM
customer AS t1
Inner Join report AS t2 ON t1.customer_id = t2.customer_id
Inner Join employee AS t5 ON t5.employee_id = t2.employee_id
Inner Join band_type AS t6 ON t6.band_type_id = t2.band_type_id
Inner Join dop AS t7 ON t7.dop_id = t2.dop_id


SELECT *
FROM   v_customers
WHERE
t2.rpt_type = 'daily' AND
t2.tmstamp >= date_sub(curdate(), interval 1 month) AND
t2.tmstamp <= curdate() AND
t1.customer_id = ''

, etc., but you'll still need to copy a select list.

P. S. What exactly is that you want to optimize? Computers these times are very good in copying strings.

Answer 2

see here

You have 2 options, build dynamic SQL or use the coalesce option.

I prefer the later (scroll half way down link to "the Coalesce function"

Be warned though that both options have their strong/weak points

Answer 3

You might want to create a view.