Return Results Of Query Based On Todays Date In Sql (mysql) Part 2

I posted another question which was resolved perfectly however I now need to apply the same code I was given to a different piece of MySQL code. What I have is SELECT value, COUNT

Solution 1:

You'll want to first JOIN the other table onto the first using related columns (I'm assuming id in the other table is related to table_c_id).

And as I had stated in my answer to your previous question, you're better off making the comparison on the bare datetime column so that the query remains sargable(i.e. able to utilize indexes):

SELECT     a.value
FROM       table_c a
INNER JOIN table_a b ON a.table_c_id = b.id
WHERE      a.table_c_id IN (9,17,25) AND
           b.crm_date_time_column >= UNIX_TIMESTAMP(CURDATE())
GROUPBY   a.value 

This assumes the crm_date_time_column will never contain times which are in the future (e.g. tomorrow, next month, etc.), but if it can, you would just add:

AND b.crm_date_time_column < UNIX_TIMESTAMP(CURDATE() + INTERVAL 1 DAY)

as another condition in the WHERE clause.

Solution 2:

SELECT   c.value
FROM     table_c c, table_a a
WHERE    c.id IN (9, 17, 25)
 AND     b.crm_date_time_column >= UNIX_TIMESTAMP(CURDATE())
 AND     c.id = a.id
GROUPBY c.value

You could do it with query like this. It selects rows from both of the tables, checks if they have same ID and current date is table_a's, crm_date_time_column. I'm not sure how do you know which rows are linking to each other in your system, so it checks there if they have the same id.