June 20, 2005 at 2:27 pm
In the script, all the schoolrequests(SLid) are listed out in tabular format. A student can click on the SLid that he/she wants and that will open up a new window with all the information pertaining to that SLid. That means I need to show all the associated data in both the academicoffice and diaryentry tables.
so....
SLsendToId, SLbillToId, SLrequestorId would be used to get AOcontactNameLast pertaining to that relation.
Also, DEkeyValue from the diaryentry table is used to get the needed records/columns from the diaryentry table(hence the WHERE DEkeyValue=SLid)
June 20, 2005 at 2:31 pm
Is this for reporting only? Or do the students can then modify that data?
June 21, 2005 at 5:45 am
The data can be modified by the students. When I do the select statement...if I ever get it working, I am going to populate a form with the returned data.
Thanks,
M
June 21, 2005 at 6:38 am
Then in that case, maybe I'd do two forms to present the data, one for each table, since anyways, you can't always update a view with multiple tables, or when possible, can be much harder.
The first table could be done like this :
where "wanted id" in (isnull(colid1,0), isnull(colid2,0), isnull(colid3,0)).
Then the second select would become very simple.
June 21, 2005 at 6:42 am
Remi,
I'm sorry, but I totally cannot comprehend this: where "wanted id" in (isnull(colid1,0), isnull(colid2,0), isnull(colid3,0)).
I've never done a query that looks even remotely like that.
So do you think I can't do this with one query?
Thanks,
M
June 21, 2005 at 6:48 am
I just don't see why. This could return 1,2 or 3 rows each time. Much simple to always return one, then use another form to edit the other possible rows.
where "place the id you need here witout quotes" in
(isnull(SLsendToId,0), isnull(SLbillToId,0), isnull(SLrequestorId,0))
June 21, 2005 at 6:51 am
On a last note.. you're no gonna get any performance from a query like this. Myabe you could use a compound index on those 3 columns and use this condition :
where (SLsendToId = @id or SLbillToId = @id or SLrequestorId = @id)
and see if it runs faster.
June 21, 2005 at 6:51 am
But doesn't the ISNULL function just replace the specified ID with a zero if it is NULL? What would this do?
Sorry to sound ignorant.
Thanks,
M
June 21, 2005 at 7:11 am
Isnull(somecol, somevalue) will force the server to do a scan of an index, or worse, the table to fetch the rows. The 2nd where condition will permit a seek to be made, which is extremely more effective of big tables. In that case the nulls don't matter because it's not in a in condition.
June 21, 2005 at 7:35 am
Remi,
Is that a built-in sql server function? I'll have to do some research on it.
Thanks,
M
June 21, 2005 at 7:44 am
Isnull is built-in. You can also lookup COALESCE.
June 21, 2005 at 7:46 am
Remi,
Ok, I looked up Isnull and I don't see how it will help with my query.
It seems like I was on the right track with my original query.
June 21, 2005 at 7:58 am
Just run my query and compare to yours... I'm just specualting on your needs. You know what you need more than me.
June 21, 2005 at 8:07 am
I'm not sure what your query is...are you replacing all of the left joins or are you just replacing the final WHERE clause so that it looks like:
SELECT SQ.*, DEdescription, DEenteredDate
, ST.AOcontactNameLast AS SendTo
, BT.AOcontactNameLast AS BillTo
, R.AOcontactNameLast AS Requestor
, DE.*
FROM schoolrequest SQ
INNER JOIN academicoffice ST ON SQ.SLsendToId=ST.AOid
INNER JOIN academicoffice BT ON SQ.SLbillToId=BT.AOid
INNER JOIN academicoffice R ON SQ.SLrequestorId=R.AOid
INNER JOIN diaryentry DE ON SQ.SLid=DE.DEkeyValue
where SQ.SLid in (isnull(SLsendToId,0), isnull(SLbillToId,0), isnull(SLrequestorId,0)).
June 21, 2005 at 8:19 am
What fields need to be updatable in the form?
Viewing 15 posts - 31 through 45 (of 58 total)
You must be logged in to reply to this topic. Login to reply