SELECT using List of IDs

,

Sometimes you have to deal with denormalized database.
And specially when links to another table are stored in one
of the fields as comma delimited list of IDs.
This script shows how to make a single SELECT statement to fetch linked records

// ---------- D.Bobkov, June 23, 2003 -----------------
// For example say we have table Stages with StagesID 
// (primary key, int), StageTasksIDs ( text ), etc. 
// and another, linked table, say Tasks with 
// TaskID ( primary key, int), Subject ( varchar(100))
// Field Stages.StageTaskIDs contains comma delimited
// string if IDs from Tasks.TaskID.
// This is the SELECT statement you can use in reports
// to fetch data from Tasks table linked to master table
// Stages:

SELECT TaskID, StagesID FROM Tasks JOIN Stages 
ON CHARINDEX( LTRIM(RTRIM(CAST(TaskID AS varchar(10)))), 
SUBSTRING(StageTaskIDs, 1, DATALENGTH(StageTaskIDs))) > 0

Rate

Share

Share

Rate