simple query part 2

ok so im still stuck, i got it partly working - i needed an extra bit :(

SELECT     Personal.ID, Personal.surname1, Closed.Dateclosed,
Closed.reasonforclosure, notes.notes, notes.dateentered
FROM         Personal LEFT OUTER JOIN
notes ON Personal.ID = notes.ID LEFT OUTER JOIN
Closed ON Personal.ID = Closed.ID
ORDER BY Personal.ID


table1

1,brown,dave
2,smith,dave


table2

1,01/01/2006,notes
1,12/01/2006,notes2
1,02/01/2006,notes3
2,01/02/2006,notesblah


table3

1,01/01/1900
2,02/01/2006



looking for query to return just return the last date and note  entered per
ID
where table3 column 2  ='01/01/1900'
result set would be

1,brown,dave,12/01/2006,notes2,01/01/1900

cheers

mark

Hello Mark,

try this:

SELECT Personal.ID, Personal.surname1, Closed.Dateclosed,
Closed.reasonforclose, notes.notes, notes.dataentered
FROM Personal
LEFT OUTER JOIN notes on Personal.ID=notes.ID
LEFT OUTER JOIN closed on Personal.ID=closed.ID
LEFT OUTER JOIN (SELECT ID,MAX(<notedate> ) as LastDate FROM notes GROUP BY
ID) LastNote on notes.ID=LastNote.ID and notes.<notedate>=LastNote.LastDate
ORDER BY Personal.ID

Please replace <notedate> with the column name for the datefield in the
notes-table.

Regards

Helmut Rieder


"luna" wrote:

> ok so im still stuck, i got it partly working - i needed an extra bit :(
>
> SELECT     Personal.ID, Personal.surname1, Closed.Dateclosed,
> Closed.reasonforclosure, notes.notes, notes.dateentered
> FROM         Personal LEFT OUTER JOIN
>                       notes ON Personal.ID = notes.ID LEFT OUTER JOIN
>                       Closed ON Personal.ID = Closed.ID
> ORDER BY Personal.ID
>
>
> table1
>
> 1,brown,dave
> 2,smith,dave
>
>
> table2
>
> 1,01/01/2006,notes
> 1,12/01/2006,notes2
> 1,02/01/2006,notes3
> 2,01/02/2006,notesblah
>
>
> table3
>
> 1,01/01/1900
> 2,02/01/2006
>
>
>
> looking for query to return just return the last date and note  entered pe
r
> ID
> where table3 column 2  ='01/01/1900'
> result set would be
>
> 1,brown,dave,12/01/2006,notes2,01/01/1900
>
> cheers
>
> mark
>
>
>

cheers helmut,

i was pretty close to that solution myself, but i was getting nowhere!!


its giving an error message now


SELECT     Personal.ID, Personal.surname1, Closed.Dateclosed,
Closed.reasonforclosure, notes.notes, notes.notes.dataentered AS Expr1
FROM         Personal LEFT OUTER JOIN
notes ON Personal.ID = notes.ID LEFT OUTER JOIN
Closed ON Personal.ID = Closed.ID LEFT OUTER JOIN
(SELECT     ID, MAX(notes.dateentered) AS LastDate
FROM          notes AS notes_1
GROUP BY ID) AS LastNote ON notes.ID =
LastNote.ID AND notes.dateentered = LastNote.LastDate
ORDER BY Personal.ID

Error Message : the multi-part identifier "notes.dateentered" could not be
bound

cheers mark,

sometimes you have the column named as dataentered and sometimes as
dateentered. I think there is the problem. Correct the name of the column to
the real one and the query should work.

Regards

Helmut


"luna" wrote:

> cheers helmut,
>
> i was pretty close to that solution myself, but i was getting nowhere!!
>
>
> its giving an error message now
>
>
> SELECT     Personal.ID, Personal.surname1, Closed.Dateclosed,
> Closed.reasonforclosure, notes.notes, notes.notes.dataentered AS Expr1
> FROM         Personal LEFT OUTER JOIN
>                       notes ON Personal.ID = notes.ID LEFT OUTER JOIN
>                       Closed ON Personal.ID = Closed.ID LEFT OUTER JOIN
>                           (SELECT     ID, MAX(notes.dateentered) AS LastDa
te
>                             FROM          notes AS notes_1
>                             GROUP BY ID) AS LastNote ON notes.ID =
> LastNote.ID AND notes.dateentered = LastNote.LastDate
> ORDER BY Personal.ID
>
> Error Message : the multi-part identifier "notes.dateentered" could not be
> bound
>
>
>
>

On Thu, 30 Mar 2006 12:52:25 GMT, luna wrote:

>cheers helmut,
>
>i was pretty close to that solution myself, but i was getting nowhere!!
>
>
>its giving an error message now
>
>
>SELECT     Personal.ID, Personal.surname1, Closed.Dateclosed,
>Closed.reasonforclosure, notes.notes, notes.notes.dataentered AS Expr1
>FROM         Personal LEFT OUTER JOIN
>                      notes ON Personal.ID = notes.ID LEFT OUTER JOIN
>                      Closed ON Personal.ID = Closed.ID LEFT OUTER JOIN
>                          (SELECT     ID, MAX(notes.dateentered) AS LastDat
e
>                            FROM          notes AS notes_1
>                            GROUP BY ID) AS LastNote ON notes.ID =
>LastNote.ID AND notes.dateentered = LastNote.LastDate
>ORDER BY Personal.ID
>
>Error Message : the multi-part identifier "notes.dateentered" could not be
>bound
>
>

Hi Luna,

In addition to Helmut's observation about datEentered / datAentered,
there's also another point:

>                          (SELECT     ID, MAX(notes.dateentered) AS LastDat
e
>                            FROM          notes AS notes_1
>                            GROUP BY ID)

Change the above to

(SELECT     ID, MAX(notes_1.dateentered) AS LastDate
FROM          notes AS notes_1
GROUP BY ID)

(In case you missed it, the change is to use the alias notes_1 in the
MAX() function instead of the table name notes - if you give a table an
alias, you can't use the table name anymore to reference its columns.

--
Hugo Kornelis, SQL Server MVP

Hi Hugo,

thanks to your attention. I don't know why I didn't see it. :o)

Regards

Helmut


"Hugo Kornelis" wrote:

> On Thu, 30 Mar 2006 12:52:25 GMT, luna wrote:
> 
>
> Hi Luna,
>
> In addition to Helmut's observation about datEentered / datAentered,
> there's also another point:
> 
>
> Change the above to
>
>        (SELECT     ID, MAX(notes_1.dateentered) AS LastDate
>         FROM          notes AS notes_1
>         GROUP BY ID)
>
> (In case you missed it, the change is to use the alias notes_1 in the
> MAX() function instead of the table name notes - if you give a table an
> alias, you can't use the table name anymore to reference its columns.
>
> --
> Hugo Kornelis, SQL Server MVP
>

thanks for  help, managed to get it working, seems to work well apart from
one thing, it doesnt
show records that havent got anything in the notes table as well, just
bashing away at that now,!
mark


"Helmut Rieder" < HelmutRieder@discuss
ions.microsoft.com> wrote in message
news:152C5F8F-DC8B-4E38-BA89- 39AD6909F56E@microso
ft.com...
> Hi Hugo,
>
> thanks to your attention. I don't know why I didn't see it. :o)
>
> Regards
>
> Helmut
>
>
> "Hugo Kornelis" wrote:
>