Is it possible to create a query involving rows from two tables like this?

Question

I would like to create a query where it selects row from a table based on few conditions which I can accomplish quite fine. However, I would like to figure out how to only show rows from table if the id of that record has not appeared in another table in the past 24 hours. How would I go about doing something like this imagine. Imagine the table was like:

ID .          | EMAIL .         | . PASS
------------- | --------------- | ---------------
1 .             [email protected] .     rgjoegeijerioge
2 .             [email protected] .   dgnkjrkjhrnjkrt
3 .             [email protected] .   etjhrnkrjkjhjhr

------------- | --------------- | --------------- | ---------------
transferID .    ACCOUNT .         ACCOUNTTO .       ITEM
------------- | --------------- | --------------- | ---------------
1 .             [email protected] .     [email protected] .     book1
2 .             [email protected] . [email protected]      book2
3 .             [email protected]  [email protected]        book2

so therefore I would want to get all rows from the first table where the email/user (or even userID if I change the table structure) hasn't been part of a row thats been added to second table in the last 24 hours.

Answer 1

You can use NOT EXISTS

 SELECT * 
   FROM table1 t1 
  WHERE NOT EXISTS ( SELECT 0 
                       FROM table2 t2
                      WHERE t2.account = t1.email 
                        AND t2.insertion_date >= DATE_ADD(NOW(), INTERVAL -1 DAY)
                      )

assuming you have a column called insertion_date in table2

Answer 2

You can use a LEFT JOIN, assuming first table is table_1 and second one is table_2,

--We only want to check the `id` against `transferid` (simplest situation):
SELECT
    table_1.*
FROM
    table_1 
    LEFT JOIN table_2 ON table_1.ID = table_2.TransferID
WHERE
    table_2.TransferID IS NULL

Second situation,

--We want to check `email` against `account` and `accountto`:

SELECT
    table_1.*
FROM
    table_1 
    LEFT JOIN table_2 
        ON table_1.Email = table_2.Account OR table_1.Email = table_2.AccountTo
WHERE
    table_2.TransferID IS NULL

Here is the db fiddle:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=5acd6dadbe6cb95f5399026f8ea3e052

Answer 3

Alternate method is JOIN like Himanshu mentioned:

Table

drop table if exists userinfo;
create table userinfo (
    id int,
    email varchar(200),
    pass varchar(200)
);
insert into userinfo values
(1, '[email protected]', ''),
(2, '[email protected]', ''),
(3, '[email protected]', '');

drop table if exists transfers;
create table transfers (
    transferid int,
    account varchar(200),
    accountto varchar(200),
    item varchar(200),
    created_date datetime
);

insert into transfers values
(1, '[email protected]', '[email protected]', 'Book1', date_sub(now(), interval 1 hour)),
(2, '[email protected]', '[email protected]', 'Book2', date_sub(now(), interval 40 hour)),
(3, '[email protected]', '[email protected]', 'Book3', date_sub(now(), interval 20 hour));

Query

select u.id, u.email
from userinfo u
left join transfers t
    on u.email = t.account
    and t.created_date >= date_sub(now(), interval 24 hour)
where t.transferid is not null;

Result

id email
1  [email protected]
2  [email protected]

Example: https://rextester.com/UYM78253

Answer 4

You will need to have a timestamp in the second table

    select * from table1
    where id not in (
                     Select id from table2 
                     where date >= now() - INTERVAL 1 DAY
                     )