Combining SELECT statements

  • Hi guys,  thanks for all the help.   I fiddled around with it and the query is "working"...meaning it is giving me no errors, but it is not returning any data.  I just get a "no records found" message, even though there actually are records there.

    Here is the query(from my ASP page). Please note that the variable "slid" is verified as exisiting AND containing the needed data before I run this query.  So while slid exists, and the data is in the DB, this query is not finding it for whatever reason.

     

    SELECT SQ.*, DEdescription, DEenteredDate

     , sendto.AOcontactNameLast AS SendTo

     , billto.AOcontactNameLast AS BillTo

     , req.AOcontactNameLast AS Requestor

     , DE.*

    FROM schoolrequest SQ

     INNER JOIN  academicoffice ST ON SQ.SLsendToId=sendto.AOid

     INNER JOIN academicoffice BT ON SQ.SLbillToId=billto.AOid

     INNER JOIN academicoffice R ON SQ.SLrequestorId=req.AOid

     INNER JOIN diaryentry DE ON SQ.SLid=DE.DEkeyValue

     AND DE.DEdeletedDate IS NULL

     WHERE SQ.SLid=" & slid & " AND DEkeyValue =" & slid

     

  • Does this return anything??

    SELECT SQ.*, DEdescription, DEenteredDate

    , sendto.AOcontactNameLast AS SendTo

    , billto.AOcontactNameLast AS BillTo

    , req.AOcontactNameLast AS Requestor

    , DE.*

    FROM schoolrequest SQ

    INNER JOIN academicoffice ST ON SQ.SLsendToId=sendto.AOid

    INNER JOIN academicoffice BT ON SQ.SLbillToId=billto.AOid

    INNER JOIN academicoffice R ON SQ.SLrequestorId=req.AOid

    INNER JOIN diaryentry DE ON SQ.SLid=DE.DEkeyValue

    AND DE.DEdeletedDate IS NULL

    WHERE SQ.SLid=DEkeyValue

  • Thanks for your quick reply Remi.  I hope you make a great deal of money dealing with SQL because you deserve it.

    Anyway, I tried your modified query and noticed something strange.  It's only returning a total of 79 records out of a total of 25,000 records!

    Just as a test, I did a:

    SELECT * FROM schoolrequest, diaryentry

    WHERE SLid=DEkeyValue

    and got all 25,000 records

    My query is just a glorfied query like the one above.  But for every SLsendToId, SLbillToId, SLrequestorId, and DEkeyValue, it returns the data corresponding to those values.

    Ugh..

    Thanks!

    M

     

  • That was my point, your query is basically asking just that.

    SQ.SLid=" & slid & " AND DEkeyValue =" & slid

    is the same as

    A = C and B = C, hence A = B... which corresponds to only 79 out of 25k records. So I would expect that you get no records almost 100% of the time.

    What are you trying to filter exactly??

  • Remi,

    The only thing I'm trying to filter out is when DEdeletedDate is not null(signalling that it was deleted).

    The query is supposed to get all the data for a specific SLid. 

    BTW the SLid=DEkeyValue part is there so I get all the data in the corresponding diary entry. Because every SLid record has a corresponding diary entry. 

    Maybe I want to do an inner join on the diaryentry table for DEkeyValue instead of using:

    WHERE SQ.WRid=DEkeyValue

     

     

  •  

    Or maybe I want to LEFT JOIN all of the columns instead of INNER JOIN??

  • or you can just rewrite the where condition like so :

    WHERE slid IN (ISNULL(SQ.SLid,0),ISNULL(DEkeyValue,0))

  • Does that replace empty records with '0'  ?

     

     

  • because " & slid & " in (null, slid) will fail

  • Replacing the INNER JOINS with LEFT JOINS gave me all the data I need EXCEPT the diary data....

     

     

  • Let's start from the beginning.

    What info are those tables containing and what info do you want the query to show (with sample data for the query)?

  • Here is one row from the schoolrequest table with corresponding rows from the diaryentry and academicoffice tables. Keep in mind that there can be multiple diary entries with the same DEkeyValue because each schoolrequest record may have more than one diary entry associated with it.

    schoolrequest:

    --------------

    SLid:                       6403

    SLsubject:                  Advanced Mathematics 302

    SLsendToId:                 250

    SLbillToId:                 310

    SLrequestorId:              462

    academicoffice

    --------------

    AOid:                       250

    AOcontactNameLast:          Jones

    academicoffice

    --------------

    AOid:                       310

    AOcontactNameLast:          Smith

    academicoffice

    --------------

    AOid:                       462

    AOcontactNameLast:          Davis

    diaryentry

    -------------

    DEid:                       21

    DEkeyValue:                 6403

    DEdescription:              Writing and modifiying computational functions

    DEenteredDate:              2004/12/10

    diaryentry

    -------------

    DEid:                       67

    DEkeyValue:                 6403

    DEdescription:              Applying functions to vector graphics

    DEenteredDate:              2005/02/23

  • And what's the problem you're trying to solve with that query?

  • The problem is, I can't get all of that information assembled correctly.  Right now, I've gotten it to the point where I can gather all the data except the diaryentry data.  That keeps on showing up blank when i run the query.

     

     

  • Let me rephrase that... what data do you need to retrieve?

Viewing 15 posts - 16 through 30 (of 58 total)

You must be logged in to reply to this topic. Login to reply