Need to convert DB2 query to TSQL

Question

I'm trying to test the first answer to this question:

SQL - message schema - need to find an existing message thread given a set of users

The first answer to this question is written in DB2 and I'm having a hard time converting the answer to TSQL. Can someone help me figure this out? Here's the query:

WITH Selected_Users(id) as (VALUES (@id1), (@id2), --etc--),
     Threads(id) as (SELECT DISTINCT threadFk
                     FROM ThreadMembers as a
                     JOIN Selected_Users as b
                     ON b.id = a.userFk)
SELECT a.id
FROM Threads as a
WHERE NOT EXISTS (SELECT '1'
                  FROM ThreadMembers as b
                  LEFT JOIN Selected_Users as c
                  ON c.id = b.userFk
                  WHERE c.id IS NULL
                  AND b.threadFk = a.id)
AND NOT EXISTS (SELECT '1'
                FROM Selected_Users as b
                LEFT JOIN ThreadMembers as c
                ON c.userFk = b.id
                AND c.threadFk = a.id
                WHERE c.userFk IS NULL)

The description of the query is part of the answer, which helps a lot. The first part of the query creates a temp table called Selected_Users, but I'm not sure how this would be done. Thanks in advance!

I updated the original question with a TSQL version. Thanks! — Redtopia, Mar 28 '12 at 23:53

Mark Brackett · Accepted Answer · 2012-03-29T14:00:43.363

2

~~I don't think T-SQL allows for the list syntax that DB2 does.~~ As Andriy M points out, SQL 2008+ does allow a pretty similar syntax:

WITH Selected_Users(id) AS (
     SELECT Id FROM (
        VALUES (@id1), (@id2), --etc--
     ) AS V(Id)
),
....

Or you could create a real temp table (or variable):

DECLARE @selected_Users TABLE (id int);
INSERT @selected_Users VALUES 
 (@id1),
 (@id2),
 --etc.--
 ; --make sure to close with semi-colon before WITH CTE

and then replace Selected_Users with @selected_Users in the rest of the query. Or change the initial CTE to:

WITH Selected_Users(id) AS (
    SELECT * FROM @selected_Users
),
....

Or, you could do a UNION ALL:

WITH Selected_Users(id) AS (
   SELECT @id1
   UNION ALL SELECT @id2 
   UNION ALL SELECT @id3
   --etc.--
),
....

edited Mar 29 '12 at 14:00

answered Mar 28 '12 at 22:13

Mark Brackett

84,552
17
108
152

Great! That helps. I'm having some problems with the column names though. – Redtopia Mar 28 '12 at 22:23
1

In SQL Server (2008+) you can't use `VALUES (...), ...` directly as a definition of a row set in a CTE, but you can SELECT from it: `SELECT * FROM (VALUES (@id1), (@id2), ...) x (v)`. That SELECT *can* be put into a CTE. – Andriy M Mar 28 '12 at 22:56

score 1 · Answer 2 · answered Mar 28 '12 at 22:03

I'm unfamiliar with DB2, but if the Selected_Users and Threads "temp tables" are supposed to be CTEs (common table expressions -- basically inline-views), then you'll have to change those to:

WITH Selected_Users(id) AS
(
    SELECT @id1 UNION
    SELECT @id2
), 
Threads(id) AS
(
    SELECT DISTINCT
        threadFk
    FROM
        ThreadMembers a
    JOIN
        Selected_Users b
        ON
        a.userFk = b.id
 )
 SELECT 
     a.Id
 FROM
     Threads a
 WHERE
 ...

I'll think about the rest and update soon.

Need to convert DB2 query to TSQL

2 Answers2