0

Table looks like that:

Users:

User DateFrom DateTo
John 1900-01-01 9999-01-01
Kyle 1900-01-01 2000-01-01

Table is quite big so I don't want to download it all to my app memory.

Now in app I have a list of users and dates. There are max 1000 of them. Now I would like to make a query to check which of those users are not in a database. I can do it one by one byt it will be 1000 queries.

For example I have an object:

User:John, Date: 2000 so the query would be like:

Select * from Users where User = 'John' and DateFrom < 2000 and DateTo > 2000

so result is: John, 1900-01-01 9999-01-01

So it means that this user is ok but for object: User:Kyle, Date: 2005

result should be none so user is not ok.

WHat would be the best approach to get information about all my users in app in a n efficient way?

3
  • I think your query was wrong with the DateFrom > 2000 as the datefrom is 1900, which is lower.
    – Peter
    Nov 22, 2022 at 9:52
  • 2
    Postgres or SQL Server? Please do not add tags for database that are not relevant
    – user1822
    Nov 22, 2022 at 10:27
  • 1
    Is the date range filter static per query?...in other words, you'll only use one date range for multiple users, each time you query the table? Also, agreed with a_horse_with_no_name, remove the unused Tag from your Post. Microsoft SQL Server != PostgreSQL.
    – J.D.
    Nov 22, 2022 at 13:30

3 Answers 3

1

You can use list of values combined with a NOT EXISTS

select t.username as missing_user_name
from (
  values ('Kyle'), ('Arthur'), ('Tricia'), ('Ford'), ('Zaphod')
) as t(username) 
where not exists (select * 
                  from users u
                  where u.user_name = t.username 
                     and DateFrom >= date '2000-01-01'
                     and DateTo < date '2001-01-01')
0

You can use a table variable or temptable to insert your search values in. Then combine that with your User table. The ones with no FoundUser are the users you want to have.

DECLARE @UsersToFind TABLE
(
    [User] VARCHAR(50),
    DateToCheck DATE
)

INSERT INTO @UsersToFind
VALUES
(
    'John', '2000-01-01'
),
(
    'Kyle', '2005-01-01'
)

SELECT utf.[User],
       utf.DateToCheck,
       u.[User] AS FoundUser,
       u.DateFrom,
       u.Dateto
FROM @UsersToFind AS utf
LEFT JOIN Users AS u ON utf.[User] = u.[User]
    AND DateFrom < utf.DateToCheck
    AND DateTo > utf.DateToCheck
0

Now in app I have a list of users and dates.

Where is that list stored?
Is that in the database as well, or something completely separate?

Why is your list of Users not in the database?
I would expect to see a "master" table of Users, which is referenced by the table you show.

Select * from Users where User = 'John' and DateFrom > 2000 and DateTo < 2000

Perhaps better:

select *  
from Users 
where User = ? 
and ? between DateFrom and DateTo 

BTW: Never use Select * in Production Code.

Now; running 1000 queries is likely to be slow.

Doing anything once takes a finite amount of time.
Doing the same thing 1000 times takes [at least] 1000 times as long.

You could build them into one, big query using in ...

where User in ( ?, ?, ?, ?, ... )

... and that will be better, but MySQL is noted for its "slow" implementation of the "in" operator (but still nowhere near as slow as 100 queries!)

Best of all would be to have a table of all Users in your database to start with!

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .