June 20, 2005 at 12:28 pm
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
June 20, 2005 at 12:31 pm
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
June 20, 2005 at 12:41 pm
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
June 20, 2005 at 12:46 pm
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??
June 20, 2005 at 12:57 pm
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
June 20, 2005 at 1:03 pm
Or maybe I want to LEFT JOIN all of the columns instead of INNER JOIN??
June 20, 2005 at 1:04 pm
or you can just rewrite the where condition like so :
WHERE slid IN (ISNULL(SQ.SLid,0),ISNULL(DEkeyValue,0))
June 20, 2005 at 1:06 pm
Does that replace empty records with '0' ?
June 20, 2005 at 1:09 pm
because " & slid & " in (null, slid) will fail
June 20, 2005 at 1:14 pm
Replacing the INNER JOINS with LEFT JOINS gave me all the data I need EXCEPT the diary data....
June 20, 2005 at 1:22 pm
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)?
June 20, 2005 at 2:14 pm
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
June 20, 2005 at 2:17 pm
And what's the problem you're trying to solve with that query?
June 20, 2005 at 2:20 pm
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.
June 20, 2005 at 2:22 pm
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