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(*) AS 'num'
WHERE table_c_id IN (9, 17, 25)
GROUP BY value
What I would like to do now is only show the results if they have been entered on the current date?
The current code snippet I have for checking the current date, which works great is (the date is in unixtime format)
( xxxxxxxxxxxxxx and curdate() = date( from_unixtime( b.crm_date_time_column ) ) )
The problem I have with query this is the date column is located in a totally different table, table_a.
How do I write the MySQL to check table_a for the date and apply the existing date SQL I have?
This is a MySQL database.
Any help will be gratefully received! This is way over my head!
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
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()) GROUP BY 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
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 GROUP BY 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.