|
|
Posted by Utahduck on 03/15/07 22:57
On Mar 14, 9:24 am, seph...@gmail.com wrote:
> 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?
You may need to do some cleaning up or better filtering. ParentID
195203 has three dates associated with it which spits out as both a
date and a date value (some normalization please?!?!?). Since you
aren't singling out any of these dates it returns all three dates for
each result... therefore the triplication.
I hope that helps somehow.
James
[Back to original message]
|