is this possible?

hi

I would like to display agregated data, as well as my image, but it seems that this requires me to put my image file field in my group by statement too, but sql does not seem to allow sorting of images. Is what I am trying to possible? Or do I have to do both things separate?

here is the code:

select tbltimesheets.weekno, sum(tblentries.hrsnorm) as hrsnorm, sum(tblentries.hrspot) as hrspot, sum(tblentries.hrsnpot) as hrsnpot, tblusers.username, images.imagefile from
tbltimesheets
inner join tblentries on tbltimesheets.tskey = tblEntries.TSKey
inner join tblusers on tbltimesheets.userkey = tblusers.userkey
inner join images on images.userkey = tblusers.userkey
group by tbltimesheets.weekno, tblusers.username, images.imagefileYou have to ddo the thing separate. YOu can use some smart SQL for this, though, working, and still do it in one query.

Or, instead summing, you could use another method (like one returning the first image in the group).

IS THIS GOOD DESIGN?

I have a 3 tables
tblAgent, tblClient, tblVendor.
Since each entity requires different info to be stored, it makes sense to
have 3 different tables.
Now each agent, client or vendor can write many notes, so I created the
table like this to store all info
ContactID, ContactTypeID, NoteEnterDate, Note
where
ContactID will contain the id of either agent, client, or vendor (since id
will be the same data type for all 3 tables)
ContactTypeID 1 = Agent
ContactTypeID 2 = Client
ContactTypeID 3 = Vendor
So based on the ContactTypeID passed in to stored procedure as parameter, I
would know which table to join and contactID will be selective to filter
data. Of course there would be no referential integrity with ContactID
since it contains ID from 3 different tables. I am planning to have a
clustered index on ContactID, ContactTypeID (table will have no primary key)
Is this good design? Should I have separate note table for each Agent,
Client, and Vendor?
Can you think of any better design with regard to indexes?
Can you think of any better design to accomodate aforementioned scenario?
ThanksLooks good enough except that
contacttypeid,contactid,notedate (in that order can) be the primary key
clustered, if your notedate is not smalldatetime, of course.
--
"Justin" wrote:

> I have a 3 tables
> tblAgent, tblClient, tblVendor.
> Since each entity requires different info to be stored, it makes sense to
> have 3 different tables.
> Now each agent, client or vendor can write many notes, so I created the
> table like this to store all info
> ContactID, ContactTypeID, NoteEnterDate, Note
> where
> ContactID will contain the id of either agent, client, or vendor (since id
> will be the same data type for all 3 tables)
> ContactTypeID 1 = Agent
> ContactTypeID 2 = Client
> ContactTypeID 3 = Vendor
> So based on the ContactTypeID passed in to stored procedure as parameter,
I
> would know which table to join and contactID will be selective to filter
> data. Of course there would be no referential integrity with ContactID
> since it contains ID from 3 different tables. I am planning to have a
> clustered index on ContactID, ContactTypeID (table will have no primary ke
y)
> Is this good design? Should I have separate note table for each Agent,
> Client, and Vendor?
> Can you think of any better design with regard to indexes?
> Can you think of any better design to accomodate aforementioned scenario?
> Thanks
>
>|||If each row in this notes table will specifically apply to only one of the
other three tables, I would say create 3 seperate tables for notes. This
lets you define the referential integrity constraints and you will know that
all client notes are in one place, vendors in another, etc. Other than the
table structures being similar, is there any other reason why you would want
these all in one table?
"Justin" <jus820@.hotmail.com> wrote in message
news:O3WqCxebGHA.1208@.TK2MSFTNGP04.phx.gbl...
> I have a 3 tables
> tblAgent, tblClient, tblVendor.
> Since each entity requires different info to be stored, it makes sense to
> have 3 different tables.
> Now each agent, client or vendor can write many notes, so I created the
> table like this to store all info
> ContactID, ContactTypeID, NoteEnterDate, Note
> where
> ContactID will contain the id of either agent, client, or vendor (since id
> will be the same data type for all 3 tables)
> ContactTypeID 1 = Agent
> ContactTypeID 2 = Client
> ContactTypeID 3 = Vendor
> So based on the ContactTypeID passed in to stored procedure as parameter,
I
> would know which table to join and contactID will be selective to filter
> data. Of course there would be no referential integrity with ContactID
> since it contains ID from 3 different tables. I am planning to have a
> clustered index on ContactID, ContactTypeID (table will have no primary
key)
> Is this good design? Should I have separate note table for each Agent,
> Client, and Vendor?
> Can you think of any better design with regard to indexes?
> Can you think of any better design to accomodate aforementioned scenario?
> Thanks
>sql