MS SQL Server - simple query part 2

Author

simple query part 2

luna

2006-03-30, 7:23 am

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

Helmut Rieder

2006-03-30, 7:23 am

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 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
>
>
>

luna

2006-03-30, 7:23 am

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

Helmut Rieder

2006-03-30, 1:23 pm

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 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
>
>
>
>

Hugo Kornelis

2006-03-30, 8:23 pm

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 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
>
>

Hi Luna,

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

> (SELECT ID, MAX(notes.dateentered) AS LastDate
> 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

Helmut Rieder

2006-03-31, 3:23 am

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
>

luna

2006-03-31, 3:23 am

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...[color=darkred]
> Hi Hugo,
>
> thanks to your attention. I don't know why I didn't see it. :o)
>
> Regards
>
> Helmut
>
>
> "Hugo Kornelis" wrote:
>

Sponsored Links