Select range date from and to and also select specific id

Question

I want to select a date range with specific provider ID and this query should print 2 data only since there's only 2 row with provider id = 1

SQL Query:

SELECT logs_tb.logs_id
      ,logs_tb.time_in
      ,logs_tb.status
      ,logs_tb.provider_id
      ,users_tb.user_first_name
      ,users_tb.user_last_name
      ,users_tb.user_contactno
      ,users_tb.user_city
      ,users_tb.user_avatar
      ,users_tb.user_email
      ,users_tb.user_contactno
      ,users_tb.user_uuid 
  FROM logs_tb
      ,users_tb
 WHERE logs_tb.provider_id = 1
   AND logs_tb.log_date BETWEEN '2022-03-25' AND '2022-03-26'
 ORDER BY logs_tb.logs_id desc;

Table:

Output:

It's only a local test and also a demo info i just dont want to share my country haha, no sensitive info has been uploaded, also emails is also a dummy not a real email i just dont want to show it thats why i censor it — Mashwishi, Mar 28 '22 at 03:19
Perhaps you should join the logs_tb and users_tb tables with a common key. Without creating a relation between the two tables you are getting all log entries crossed by all users, whether or not those users were related to the log entry at all. This is the probable cause of your 'too many rows returned'. — Chris Maurer, Mar 28 '22 at 03:23
why are you not using `ON` condition? i think you should check this link `https://stackoverflow.com/questions/16470942/how-to-use-mysql-join-without-on-condition` — M.Hemant, Mar 28 '22 at 03:23

score 0 · Accepted Answer · answered Mar 28 '22 at 03:29

I used ON condition, thank you M.Hermant

Query:

SELECT logs_tb.logs_id, logs_tb.time_in, logs_tb.status, logs_tb.provider_id, users_tb.user_first_name, users_tb.user_last_name, users_tb.user_contactno, users_tb.user_city, users_tb.user_avatar, users_tb.user_email, users_tb.user_contactno, users_tb.user_uuid 
FROM users_tb
INNER JOIN logs_tb
ON users_tb.user_id = logs_tb.user_id
WHERE (logs_tb.log_date BETWEEN '2022-03-25' AND '2022-03-26') AND logs_tb.provider_id = 1
ORDER BY logs_tb.log_date desc;

Output:

Select range date from and to and also select specific id

1 Answers1