May 9, 2006 at 3:15 am
Hi, I'm looking for some advice on the best way to write this type of query.
I have 3 tables...
Table1: T1ID (PK)
Table2: T2ID (PK), T2Amount & T2Date
Table3: T1ID (PK), T2ID (PK)
Table3 is used to resolve a many:many relationship between Tables 1 & 2.
A Table2 record will usually relate to no more than 6 Table1 records, but in theory there is no limit.
I want to report on Table2 records within a date range, & include a single column that lists the related Table1ID's, along the lines of...
Table2ID T2Amount Table1IDs
123 1,000.00 456, 789
456 500.00 765
789 2,500.00 234, 345, 987
I can write this using a cursor & build the TableIDs string as I go, but is there a better way within SQL - even if I have to be constrained by a maximum number (say 6) of related Table1 records?
Thanks in advance.
May 9, 2006 at 6:47 am
Write a function to concatenate T1ID's (plenty of examples on theis site) or if you do not wish to use a function then this will do it (limited to 6 IS's though)
SELECT T2ID, T2Amount,
SUBSTRING(COALESCE(', ' + I1,'') + COALESCE(', ' + I2,'') + COALESCE(', ' + I3,'') +
COALESCE(', ' + I4,'') + COALESCE(', ' + I5,'') + COALESCE(', ' + I6,''),3,255)
FROM (SELECT t2.T2ID, t2.T2Amount,
MAX(CASE WHEN t1.rowid = 1 THEN CAST(t1.T1ID as varchar) ELSE null END) AS [I1],
MAX(CASE WHEN t1.rowid = 2 THEN CAST(t1.T1ID as varchar) ELSE null END) AS [I2],
MAX(CASE WHEN t1.rowid = 3 THEN CAST(t1.T1ID as varchar) ELSE null END) AS [I3],
MAX(CASE WHEN t1.rowid = 4 THEN CAST(t1.T1ID as varchar) ELSE null END) AS [I4],
MAX(CASE WHEN t1.rowid = 5 THEN CAST(t1.T1ID as varchar) ELSE null END) AS [I5],
MAX(CASE WHEN t1.rowid = 6 THEN CAST(t1.T1ID as varchar) ELSE null END) AS [I6]
FROM @Table2 t2
INNER JOIN @Table3 t3 ON t3.T2ID = t2.T2ID
INNER JOIN (SELECT x1.T1ID, (SELECT COUNT(*) FROM @Table1 x2
WHERE x2.T1ID <= x1.T1ID) AS [rowid] FROM @Table1 x1) t1
ON t1.T1ID = t3.T1ID
GROUP BY t2.T2ID, t2.T2Amount) a
ORDER BY T2ID, T2Amount
Far away is close at hand in the images of elsewhere.
Anon.
May 10, 2006 at 5:27 am
Curosor solution is most probably faster then one mentioned above.
Another approach is to build table variable (or temp table) and with tabl21IDs column initally null. Then you can update that column using approach described in: http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp
I guess you are not concerned with the order of ids but you need to use two variables in update statement @currentTable2ID and @table1IDs to achive what you want.
May 10, 2006 at 6:17 am
This should work in TSQL but is non-ANSI:
create function dbo.GetTable1IDs
(
@T2ID int
)
returns varchar(4000)
begin
declare @Result varchar(4000)
set @Result = ''
select @Result = @Result + cast(T1ID as varchar(10)) + ', '
from dbo.table3
where T2ID = @T2ID
return @Result
end
go
select T2ID
,T2Amount
,dbo.GetTable1IDs(T2ID)
from Table2
May 10, 2006 at 7:16 am
Look into table variables. Same concept as cursors (row by row), but less intensive on the machine resources. Hope that this helps. Thanks.
Chris
May 11, 2006 at 1:41 am
Many thanks for the replies, your help is much appreciated.
May 11, 2006 at 7:34 am
You're welcome.
May 12, 2006 at 2:21 am
Be careful with table variables though - with many rows they are held on disc anyway so no i/o gain and they cannot be handled by parallel execution plans on multi cpu machines.
May 12, 2006 at 3:31 am
and statistics cannot be mintained on them, so the optimiser has little to no idea how many rows are in the table variable when it generates a query plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply