DAY() not working with 'Left Join'?

I have two tables. Days (1-31) and dates (random dates)

If I have a query that is

Select Day, Date

From days LEFT JOIN dates ON days.Day = DAY(dates.date)

Order By Day, Date

The left join will not return all the days in days just the ones that join with dates. It returns as if I am doing and 'Inner join'. What do I need to do different?

Thanks.

ry this:

Select a.Day, b.date

From days a LEFT JOIN dates b ON a.Day = DAY(b.date)

Order By a.Day, b.date

|||That didn't seem to do anything. What was the thought behind this if you don't mind?|||

CREATE TABLE [dbo].[Dates]([Date] [datetime] NULL,

[id] [int] NULL)

INSERT INTO [Dates] ([Date],[id])VALUES('Oct 2 2006 12:00:00:000AM',1)
INSERT INTO [Dates] ([Date],[id])VALUES('Oct 4 2006 12:00:00:000AM',2)

CREATE TABLE [Days]([Day] [int] NULL)

INSERT INTO [Days] ([Day])VALUES(1)
INSERT INTO [Days] ([Day])VALUES(2)
INSERT INTO [Days] ([Day])VALUES(3)
INSERT INTO [Days] ([Day])VALUES(4)
INSERT INTO [Days] ([Day])VALUES(5)
INSERT INTO [Days] ([Day])VALUES(6)
INSERT INTO [Days] ([Day])VALUES(7)
INSERT INTO [Days] ([Day])VALUES(8)
INSERT INTO [Days] ([Day])VALUES(9)
INSERT INTO [Days] ([Day])VALUES(10)
INSERT INTO [Days] ([Day])VALUES(11)
INSERT INTO [Days] ([Day])VALUES(12)
INSERT INTO [Days] ([Day])VALUES(13)
INSERT INTO [Days] ([Day])VALUES(14)
INSERT INTO [Days] ([Day])VALUES(15)
INSERT INTO [Days] ([Day])VALUES(16)
INSERT INTO [Days] ([Day])VALUES(17)
INSERT INTO [Days] ([Day])VALUES(18)
INSERT INTO [Days] ([Day])VALUES(19)
INSERT INTO [Days] ([Day])VALUES(20)
INSERT INTO [Days] ([Day])VALUES(21)
INSERT INTO [Days] ([Day])VALUES(22)
INSERT INTO [Days] ([Day])VALUES(23)
INSERT INTO [Days] ([Day])VALUES(24)
INSERT INTO [Days] ([Day])VALUES(25)
INSERT INTO [Days] ([Day])VALUES(26)
INSERT INTO [Days] ([Day])VALUES(27)
INSERT INTO [Days] ([Day])VALUES(28)
INSERT INTO [Days] ([Day])VALUES(29)
INSERT INTO [Days] ([Day])VALUES(30)
INSERT INTO [Days] ([Day])VALUES(31)

And the script that works:

Select a.Day, b.date

From days a LEFT JOIN dates b ON a.Day = DAY(b.date)

Order By a.Day, b.date

If you cannot run this, let's see what is the problem again.

|||

So I get to playing around with your example and descovered some stuff I didn't know about left joins.

My query has touble when I add a Where clause on it to filter dates to a certain range. The differents querys are below in case someone else needs help. Thanks.

NOT WORKING

Select a.Day, b.date

From days a LEFT JOIN dateshiftcrewTable b ON a.Day = DAY(b.date)

Where b.date Between '1/1/1999' and '4/4/1999'

Order By a.Day, b.date

WORKING

Select a.Day, b.date

From days a LEFT JOIN dateshiftcrewTable b ON a.Day = DAY(b.date) and

b.date Between '1/1/1999' and '4/4/1999'

Order By a.Day, b.date

|||

If you use a subquery with a where clause fro your LEFT JOIN, it should work.

Select a.Day, b.date

From days a LEFT JOIN (select * FROM dateshiftcrewTable Where date Between '1/1/1999' and '4/4/1999') b ON a.Day = DAY(b.date)

Order By a.Day, b.date