|
|
Posted by sephent on 03/14/07 15:24
I'm pretty new to SQL and querying, and I'm not sure if this is the
correct group to post this to, but hopefully someone can point me in
the right direction.
I have the following query:
SELECT Distinct dbo.DLFolders.FolderCode,
dbo.PropertyCharValues.ParentId,
dbo.PropertyCharValues.DocumentTypePropertyId,
dbo.PropertyCharValues.PropertyCharValue,
dbo.PropertyDateValues.DocumentTypePropertyId AS Expr1,
dbo.PropertyDateValues.PropertyDateValue
FROM dbo.PropertyDateValues INNER JOIN
dbo.PropertyCharValues ON
dbo.PropertyDateValues.ParentId = dbo.PropertyCharValues.ParentId
INNER JOIN
dbo.Documents ON dbo.PropertyCharValues.ParentId
= dbo.Documents.DocumentId INNER JOIN
dbo.DLFolders ON dbo.Documents.DLFolderID =
dbo.DLFolders.FolderID
WHERE (dbo.PropertyDateValues.ParentId = '195203')
Which yields these results (except the far right column, which I've
added manually to describe what the PropertyCharValue is. I didn't
bother with the PropertyDateValues, but they are basically Invoice
Date, Post Date, and Check Date):
FolderCode ParentId DocumentTypePropertyId PropertyCharValue Expr1
PropertyDateValue
428 195203 1 19 4 2006-06-03 00:00:00.000 Vendor Number
428 195203 1 19 6 2006-07-05 00:00:00.000 Vendor Number
428 195203 1 19 93 2006-06-30 00:00:00.000 Vendor Number
428 195203 2 Bhellington Corporation 4 2006-06-03 00:00:00.000 Vendor
Name
428 195203 2 Bhellington Corporation 6 2006-07-05 00:00:00.000 Vendor
Name
428 195203 2 Bhellington Corporation 93 2006-06-30 00:00:00.000
Vendor Name
428 195203 3 68001-0421381 4 2006-06-03 00:00:00.000 Invoice Number
428 195203 3 68001-0421381 6 2006-07-05 00:00:00.000 Invoice Number
428 195203 3 68001-0421381 93 2006-06-30 00:00:00.000 Invoice Number
428 195203 5 0000000052 4 2006-06-03 00:00:00.000 Voucher Number
428 195203 5 0000000052 6 2006-07-05 00:00:00.000 Voucher Number
428 195203 5 0000000052 93 2006-06-30 00:00:00.000 Voucher Number
428 195203 7 0000010027 4 2006-06-03 00:00:00.000 Check Number
428 195203 7 0000010027 6 2006-07-05 00:00:00.000 Check Number
428 195203 7 0000010027 93 2006-06-30 00:00:00.000 Check Number
428 195203 22 428 4 2006-06-03 00:00:00.000 Company Code
428 195203 22 428 6 2006-07-05 00:00:00.000 Company Code
428 195203 22 428 93 2006-06-30 00:00:00.000 Company Code
428 195203 23 3908.78 4 2006-06-03 00:00:00.000 Amount
428 195203 23 3908.78 6 2006-07-05 00:00:00.000 Amount
428 195203 23 3908.78 93 2006-06-30 00:00:00.000 Amount
428 195203 24 APVO-0000015 4 2006-06-03 00:00:00.000 Batch ID
428 195203 24 APVO-0000015 6 2006-07-05 00:00:00.000 Batch ID
428 195203 24 APVO-0000015 93 2006-06-30 00:00:00.000 Batch ID
428 195203 25 040575000 4 2006-06-03 00:00:00.000 GL Accounts
428 195203 25 040575000 6 2006-07-05 00:00:00.000 GL Accounts
428 195203 25 040575000 93 2006-06-30 00:00:00.000 GL Accounts
428 195203 25 040576000 4 2006-06-03 00:00:00.000 GL Accounts
428 195203 25 040576000 6 2006-07-05 00:00:00.000 GL Accounts
428 195203 25 040576000 93 2006-06-30 00:00:00.000 GL Accounts
428 195203 123 428 4 2006-06-03 00:00:00.000 Target Company
428 195203 123 428 6 2006-07-05 00:00:00.000 Target Company
428 195203 123 428 93 2006-06-30 00:00:00.000 Target Company
As you can see, the results triplicate the PropertyCharValue items and
multiply by 11 the PropertyDateValues because they are on different
tables and because I don't know what I'm doing.
I would like to see a result with columns like this:
FolderCode ParentID VendorNum VendorName InvNum VouchNum
CompCode Amount BatchID GLAccounts GLAccounts(2) Target
Company InvDate PostDate CheckDate
To that end, I tried the following query as an attempt in that
direction:
Declare @IDNum int
Set @IDNum = '195203'
SELECT distinct dbo.DLFolders.FolderCode,
dbo.PropertyCharValues.ParentId,
(Select PropertyCharValue from PropertyCharValues Where
ParentId = '195203' and DocumentTypePropertyId= '5') as VouchNum,
(Select PropertyCharValue from PropertyCharValues Where
ParentID = '195203' and DocumentTypePropertyID= '25') as GLNumber,
--dbo.PropertyCharValues.DocumentTypePropertyId,
dbo.PropertyCharValues.PropertyCharValue,
(SELECT PropertyDateValue
FROM dbo.PropertyDateValues
WHERE ParentId = '195203' AND
DocumentTypePropertyID = '4') AS InvoiceDate,
(SELECT PropertyDateValue
FROM dbo.PropertyDateValues
WHERE ParentId = '195203' AND
DocumentTypePropertyID = '93') AS PostDate,
(SELECT PropertyDateValue
FROM dbo.PropertyDateValues
WHERE ParentId = '195203' AND
DocumentTypePropertyID = '6') AS CheckDate
FROM dbo.PropertyDateValues INNER JOIN
dbo.PropertyCharValues ON
dbo.PropertyDateValues.ParentId = dbo.PropertyCharValues.ParentId
INNER JOIN
dbo.Documents ON dbo.PropertyCharValues.ParentId
= dbo.Documents.DocumentId INNER JOIN
dbo.DLFolders ON dbo.Documents.DLFolderID =
dbo.DLFolders.FolderID
WHERE (dbo.PropertyDateValues.ParentId = '195203')
And I discovered that a subquery can't have more than one result.
I'm not sure what avenues to try next. Any suggestions?
[Back to original message]
|