Combining SELECT statements

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

     

  • Is this for reporting only? Or do the students can then modify that data?

  • 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

     

     

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

  • 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

     

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

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

  •  

    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

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

  • Remi,

    Is that a built-in sql server function?  I'll have to do some research on it.

     

    Thanks,

    M

     

  • Isnull is built-in. You can also lookup COALESCE.

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

  • Just run my query and compare to yours... I'm just specualting on your needs. You know what you need more than me.

  • 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)).

  • 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