CODEWITHSUNDEEP
mysqlsql
user2338925
user2338925

Reputation:

(MySql) select query returns empty row in WHERE and AND condition

I have two related table,a one to many relations.I can display values on a selected column in where condition

enter image description here

What is the correct way when adding 'AND' clause in the above query?I tried this queries but it result empty set.

enter image description here

Upvotes: 1

Views: 999

Answers (2)

user2338925
user2338925

Reputation:

I found a solution to my problem. I change my query into 

SELECT 
      date_created 
FROM collections 
WHERE DATE(date_created) = '2014-03-12' AND loan_id = 3942;

Now it returns rows with the specific dates.

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270765

The values in date_created all include a time component 08:00:00. However, you are comparing it as equal to the date value '2014-03-13' which implies 2014-03-12 00:00:00, and is therefore not equal to 2014-03-12 08:00:00

To make that comparison the way you are attempting, you need to truncate the value in date_created to only the date portion, removing the time with MySQL's native DATE() function.

SELECT date_created 
FROM collections
WHERE 
  -- Truncate the datetime to a date only
  DATE(date_created) = '2014-03-12'
  AND loan_id = 3942

The above example is that of your first query attempt, but using the JOIN requires the same solution in the WHERE clause.

Upvotes: 4

Related Questions