SQL Query

[benchew0904]

Anyone can help me with a SQL query?

 

I want to retrieve data from my database which must be 2 days before current date.

I need to use a JOIN as there's 2 table.

 

Table 1

u_id | name | email

 

Table 2

id | u_id | date

* For table 2 u_id is the same as table 1 u_id

 

I tried using this query but it dosen't seems to work

select * from table1 t1 JOIN table2 t2 ON t1.u_id = t2.u_id where t2.date <= (DATE('d-m-y')) and t2.date >= ( DATE('d-m-y') - INTERVAL 2 day )

[SergeiSS]

What do you mean "it dosen't seems to work"? Do you have any error or you don't have an expected result? And - do you use MySQL or other SQL?

[benchew0904]

I have a few data in my database which the dates are 2 days before today date

I tried that query on my localhost but it didn't fetch any data..

[SergeiSS]

First... You didn't answer - what type of SQL server do you use.

 

What is a type of you 'date' field? And one more question - why do you use reserved word for field name? It's possible, but is not recommended.

 

Then. Try this query

select * from table2 where date between ( DATE('d-m-y') - INTERVAL 2 day ) and DATE('d-m-y')

if you use MySQL try this

 

select * from `table2` where `date` between ( DATE('d-m-y') - INTERVAL 2 day ) and DATE('d-m-y')

 

[benchew0904]

Sorry, i'm using mysql.

 

I want to retrive email, u_id from table 1 when the date from table 2 is 2 days before current date.

Earlier on, I tried this query and it fetch me all data from my database.

SELECT * FROM table1 t1 JOIN table2 t2 ON t1.u_id = t2.u_id WHERE t2.date <= CURRENT_DATE AND t2.date <= (CURRENT_DATE -  INTERVAL 2 DAY )

 

I tried both your query and it's also did not fetch any data out.

[SergeiSS]

In the second query you use this condition "t2.date <= CURRENT_DATE AND t2.date <= (CURRENT_DATE -  INTERVAL 2 DAY )". What does it mean? It means that you like to see all dates that a less than 2 days before today. Check all conditions and you'll understand it.

 

But it your start question you wrote ANOTHER condition: dates might be between 2 days before today and current date. Do you see the difference?

 

It means that your dates are older than 2 days before...

 

BTW... Try this query and you'll see all dates in your table

select distinct `date` from `table2` order by `date` asc

[benchew0904]

Sorry, I have confused myself as i have been struggling with this query for a few days already.

 

I just realized my type for date i used varchar. I just change back to date and all the date are in this format (yyyy-mm-dd, e.g. 2012-01-01).

 

I rephrase my question and maybe you can let me know which query I should use as I think i have confused myself.

E.g. I have these few dates in my database. 2011-12-31, 2012-01-02, 2012-01-03, 2012-01-03, 2012-01-03, 2012-01-04

I just want want to retreive these few dates, 2012-01-03, 2012-01-03, 2012-01-03, and the email from table 1 which is 2 days before today or should I say 2 days after today date?

[SergeiSS]

I just want want to retreive these few dates, 2012-01-03, 2012-01-03, 2012-01-03, and the email from table 1 which is 2 days before today or should I say 2 days after today date?

2 days before exact date, including this date

where `date` between ( DATE('d-m-y') - INTERVAL 2 day ) and DATE('d-m-y') 

 

2 days after exact date, including this date

where `date` between DATE('d-m-y')  and ( DATE('d-m-y') + INTERVAL 2 day )

 

If you change DATE('d-m-y')  to CURRENT_DATE you'll get dates relatively to current date.

[benchew0904]

Thanks a lot.

You have helped me solve my problem.

 

Really appreciate it..

[SergeiSS]

You are welcome