Sorin

>There is only one table with the cars numbers and the dates.
>There isn't a separate tasks table to join.

What Adam suggested is a self join - joining the single table to itself 11 times to get the other 11 columns. Try it you'll be surprised.

Roy Lambert [Team Elevate]

Roy

>
> What Adam suggested is a self join - joining the single table to itself 11 times to get the other 11 columns. Try it
you'll be surprised.
>

Except he didn't (ie select from cars join tasks).  Select from cars join cars t1 etc will work, but I think if there is
a month missing for a car it will be excluded completely.

John

John

I would now remove my foot from my mouth

but if you correct for

<<There is only one table with the cars numbers and the dates.
There isn't a separate tasks table to join.>>

I'm sure Adam really meant a self join

Roy Lambert

Roy
>
> I would now remove my foot from my mouth
>

I thought a man who used to write APL would appreciate the brevity <bg>

John

John

>I thought a man who used to write APL would appreciate the brevity <bg>

That's a very impressive memory you have there.

Roy Lambert

> I'm sure Adam really meant a self join

Ok, I check the solutions again and John solution indeed work.

About Adam I can't figure what you mean by self join.
I try it and it raise an error on the line "LEFT JOIN Tasks"
with the error "table Tasks does not exists..." and it's realy doesn't exist
All the data is on the cars table. Where the Tasks table coming from?

Thanks
Sorin

Sorin

>> I'm sure Adam really meant a self join
>
>Ok, I check the solutions again and John solution indeed work.
>
>About Adam I can't figure what you mean by self join.
>I try it and it raise an error on the line "LEFT JOIN Tasks"
>with the error "table Tasks does not exists..." and it's realy doesn't exist
>All the data is on the cars table. Where the Tasks table coming from?

Self join means joining a table to itself. Adam made a bit of a mistake and thought there were two tables when there's only one. So rather than

SELECT
 C.CarNumber,
 T1.TaskDate as Jan_Date,
 T2.TaskDate as Feb_Date,
 [ ... etc. for all other months  ... ]

FROM Cars C
LEFT JOIN Tasks T1 ON T1.CarNumber = C.CarNumber
LEFT JOIN Tasks T2 ON T2.CarNumber = C.CarNumber

WHERE T1.TaskDate BETWEEN '2010-01-01' and '2010-01-31'
AND  T2.TaskDate BETWEEN '2010-02-01' and '2010-02-31'
[etc. for the other months]

you'd have something like

SELECT
 C.CarNumber,
 T1.DateDone as Jan_Date,
 T2.DateDone as Feb_Date,
 [ ... etc. for all other months  ... ]

FROM Cars C
LEFT JOIN Cars T1 ON T1.CarNumber = C.CarNumber
LEFT JOIN Cars T2 ON T2.CarNumber = C.CarNumber

WHERE T1.DateDone BETWEEN '2010-01-01' and '2010-01-31'
AND  T2.DateDone BETWEEN '2010-02-01' and '2010-02-31'
[etc. for the other months]


Roy Lambert [Team Elevate]

Thanks Roy

Yes this work but John's is right. When a month is missing all the carnumber
row is excluded.
John solution also run faster.

On any case I learn something new.

Thanks you all
Sorin


> Self join means joining a table to itself. Adam made a bit of a mistake
> and thought there were two tables when there's only one. So rather than
>
> SELECT
>  C.CarNumber,
>  T1.TaskDate as Jan_Date,
>  T2.TaskDate as Feb_Date,
>  [ ... etc. for all other months  ... ]
>
> FROM Cars C
> LEFT JOIN Tasks T1 ON T1.CarNumber = C.CarNumber
> LEFT JOIN Tasks T2 ON T2.CarNumber = C.CarNumber
>
> WHERE T1.TaskDate BETWEEN '2010-01-01' and '2010-01-31'
> AND  T2.TaskDate BETWEEN '2010-02-01' and '2010-02-31'
> [etc. for the other months]
>
> you'd have something like
>
> SELECT
>  C.CarNumber,
>  T1.DateDone as Jan_Date,
>  T2.DateDone as Feb_Date,
>  [ ... etc. for all other months  ... ]
>
> FROM Cars C
> LEFT JOIN Cars T1 ON T1.CarNumber = C.CarNumber
> LEFT JOIN Cars T2 ON T2.CarNumber = C.CarNumber
>
> WHERE T1.DateDone BETWEEN '2010-01-01' and '2010-01-31'
> AND  T2.DateDone BETWEEN '2010-02-01' and '2010-02-31'
> [etc. for the other months]

Thanks for clarifying Roy,

You are right ... I thought there were 2 tables (there should be for good DB design!) & in the absence of 2 tables a self join is the way to go.

However, I agree with the other post that the programmer has to be careful of NULLS as these can result in empty rows if 1 element is returned NULL.

Self Joins are one of the "does that really work?" logical head-scratching moments of SQL ... they do work & they can be really, really useful.

Adam