CODEWITHSUNDEEP
sqlsql-server
Mexxer
Mexxer

Reputation: 1777

SQL query: Join two tables with where clause

I have a problem with a SQL query. I got the following query working, which pretty basic:

SELECT *
FROM table1
WHERE Date = CURDATE()

There's a column in table1 which is called h_id and a table2 with the columns h_id and name

I want to join those two tables so that I have the names out of table 2 from the current date.

I tried this, but it doesn't seem to work

SELECT t2.name, t1.field2
FROM table1 t1
INNER JOIN table2 t2 ON t1.H_ID = t2.H_ID
WHERE t1.Date = CURDATE( )

Upvotes: 6

Views: 124177

Answers (4)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

SQL Server does not support curdate(). That's for MySQL.

The appropriate function is getdate()

Here is a link to the official documentation

Edit:

As you said in a comment, if getdate()doesn't work then you're using MySQL, then. Try this:

SELECT t2.name, t1.field2
FROM table1 t1
INNER JOIN table2 t2 ON t1.H_ID = t2.H_ID
WHERE date(t1.Date) = date(CURDATE())

Upvotes: 2

user359040
user359040

Reputation:

Try:

SELECT t2.name, t1.field2
FROM table1 t1
LEFT JOIN table2 t2 ON t1.H_ID = t2.H_ID
WHERE t1.[Date] = GETDATE( )

or alternatively (MySQL):

SELECT t2.name, t1.field2
FROM table1 t1
LEFT JOIN table2 t2 ON t1.H_ID = t2.H_ID
WHERE t1.`Date` = CURDATE( )

Upvotes: 0

Diego
Diego

Reputation: 36126

try to remove the WHERE t1.Date = CURDATE( ) and see if your record is returned. If it is, there is a problem with your CURDATE( ), try using getdate() or try to format the date

Upvotes: 2

aF.
aF.

Reputation: 66687

It might be case sensitive.

or

does table1 have field2 column?

If not/so, and according to your question, try it like this:

SELECT t2.name
FROM table1 t1
INNER JOIN table2 t2 ON t1.h_id = t2.h_id
WHERE t1.Date = CURDATE()

Another possibility is the where clause, try it like this:

SELECT t2.name
FROM table1 t1
INNER JOIN table2 t2 ON t1.h_id = t2.h_id
WHERE convert(varchar, t1.Date, 112) = convert(varchar, getdate(), 112)

A last possibility is that there isn't any h_id equal from table1 and table2.

Upvotes: 10

Related Questions