Join 2 tables SQL query

Question

I'm trying to write a query for Select from 2 tables.

Tables are the following:

Table_1: 
id (int)
name (varchar)
status int (0,1)

Table_2: 
id (int)
table_1_id (int)
name (varchar)
time (datetime)

I need to select all the rows from Table_2 which are no older than 1 day and that are associated with table_1 with status 1. The way I do it now is using 2 queries and 2 foreach arrays, which is very inefficient. Could someone help me to write a query with join? Thank you for your time.

Answer 1

Do not need 2 queries. You can use 1 query as:

SELECT t2.* FROM Table_1 t1, Table_2 t2
WHERE t1.id = t2.table_1_id AND
    t1.status = 1 AND
    DATE(t2.'time') >= DATE(now() - INTERVAL 1 DAY)

Because you want

I need to select all the rows from Table_2 which are no older than 1 day

so we must have greater than or equal operator:

DATE(t2.'time') >= DATE(now() - INTERVAL 1 DAY)

Answer 2

SELECT table_2.* FROM table_1 t1 INNER JOIN table_2 t2 ON t2.table_1_id=t1.id 
WHERE t1.status=1 AND time < (NOW() - INTERVAL 1 DAY);

You have to use ON to join tables since the fields in question do not have the same name. Otherwise you could have joined with USING(id_field). In your case inner join is probably most useful. You could have used left join if you wanted matching results from table_1 even if there is no counterpart in table_2, e.g.

Answer 3

No need of looping, you can do a JOIN between the tables like

select t2.*
from Table_2 t2 join Table_1 t1 on t2.table_1_id = t1.id
where t1.status = 1
and date(t2.`time`) = date(now() - interval 1 day);