Join Query on Datetime field

Question

I need to write the query based on the below condition.

I have two tables , in that i need to fetch the all the columns from table1 and one more column from another field based on the date field. Date 1 field in Table 1 is in Datetime format, Date2 field in table 2 id in date Format.

I am assuming the below query , please update me is there any changes

Select Table1.*, c3 
From Table1
Full Outer join
Table2 
On Date(Date1) = Date2

here I'm using the Date function to convert the datetime field to date format.
C3 is column in Table 2.

Please confirm me which join i should use?

See this: http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins?rq=1 It will answer your question. — jpw, Mar 14 '15 at 15:44

score 1 · Answer 1 · answered Mar 14 '15 at 15:58

Your ON clause looks correct. Your specification seems to call for a JOIN (inner join) or a LEFT JOIN. MySQL doesn't have FULL OUTER JOIN. In any rate it probably doesn't make sense for your application. (But that's a guess on my part.)

Try this.

       SELECT t1.*, t2.c3
         FROM Table1 t1
    LEFT JOIN Table2 t2 ON Date(t1.Date1) = t.Date2

That will give you every row in Table1, and the associated c3 values from Table2. If any given row in Table1 lacks a corresponding row in Table2, you'll get a NULL for the c3 value (that's what LEFT JOIN does).

Join Query on Datetime field

1 Answers1