December 5, 2007 at 5:01 am
Hi,
I've looked at the past postings on exclusion lists, but cannot get my logic to work. The complete SQL scripts are attached below.
This is my basic scenario. My actual problem has many other tables in the join, but I have stripped it down to the basic ones.
- Table 1: Represents IDs of files available for download. 4 records keyed on DownloadID
- Table 2: Represents IDs of files downloaded. Each file in Table 1 can be downloaded by multiple users. This table contains 6 records, pertaining to ONLY 2 of the records in Table1.
- Table 3: Represents all of files available for download, with their download status. As a result it should return the 6 records from Table2, along with the 2 records from Table1 that have not been downloaded. INSTEAD, the logic seems to return 12 records from Table2, with each record duplicated.
This is driving me insane! Any help would be greatly appreciated. Thanks
--CREATE TEMP TABLES
CREATE TABLE #Table1
(
DownloadID INT IDENTITY
,SessionId INT NOT NULL
)
CREATE TABLE #Table2
(
[DownloadHistoryID] INT IDENTITY
,DownloadID INT
,UserID INT
,UserName VARCHAR(10)
,Downloaded BIT
)
CREATE TABLE #Table3
(
DownloadID INT
,DownloadHistoryID INT NULL
,SessionId INT NOT NULL
,UserName VARCHAR(10) NULL
,Downloaded BIT NULL
)
INSERT INTO #Table1 (SessionID)
VALUES (1000)
INSERT INTO #Table1 (SessionID)
VALUES (2000)
INSERT INTO #Table1 (SessionID)
VALUES (3000)
INSERT INTO #Table1 (SessionID)
VALUES (4000)
INSERT INTO #Table2 (DownloadID, UserID, UserName, Downloaded)
VALUES (1,100, 'USER_A', 1)
INSERT INTO #Table2 (DownloadID, UserID, UserName, Downloaded)
VALUES (1,200, 'USER_B', 0)
INSERT INTO #Table2 (DownloadID, UserID, UserName, Downloaded)
VALUES (1,300, 'USER_C', 0)
INSERT INTO #Table2 (DownloadID, UserID, UserName, Downloaded)
VALUES (2,100, 'USER_A',1)
INSERT INTO #Table2 (DownloadID, UserID, UserName, Downloaded)
VALUES (2,200, 'USER_B',1)
INSERT INTO #Table2 (DownloadID, UserID, UserName, Downloaded)
VALUES (2,400, 'USER_D',0)
/**/
SELECT * FROM #Table1
SELECT * FROM #Table2
/* DOWNLOADED FILES - 2 RECORDS */
INSERT INTO #Table3 (DownloadID, DownloadHistoryID, SessionID, UserName, Downloaded)
(
SELECT T1.DownloadID, T2.DownloadHistoryID, T1.SessionID, T2.UserName, T2.Downloaded
FROM #Table1 T1
INNER JOIN #Table2 T2
ON T1.DownloadID = T2.DownloadID
WHERE T2.Downloaded <> 0
)
/* NOT DOWNLOADED FILES ASSOCIATED TO DOWNLOADED FILES - 4 RECORDS */
INSERT INTO #Table3 (DownloadID, DownloadHistoryID, SessionID, UserName, Downloaded)
(
SELECT T1.DownloadID, T2.DownloadHistoryID, T1.SessionID, T2.UserName, T2.Downloaded
FROM #Table1 T1
INNER JOIN #Table2 T2
ON T1.DownloadID = T2.DownloadID
WHERE T2.Downloaded = 0
)
/* NOT DOWNLOADED FILES ASSOCIATED TO DOWNLOADED FILES - 2 RECORDS: DownloadID = 3, 4 */
/* This stament is also inserting duplicates for DownloadID = 1, 2 so that records returned are 8, rather than 2 */
INSERT INTO #Table3 (DownloadID, DownloadHistoryID, SessionID, UserName, Downloaded)
(
SELECT T1.DownloadID, T3.DownloadHistoryID, T1.SessionID, T3.UserName, T3.Downloaded
FROM #Table1 T1
LEFT JOIN #Table3 T3
ON T1.DownloadID = T3.DownloadID
)
SELECT * FROM #Table3
ORDER BY DownloadID
--DROP TEMP TABLES
DROP TABLE #Table3
DROP TABLE #Table2
DROP TABLE #Table1
December 5, 2007 at 10:11 am
Hi,
sorted it with the help of a colleague. Makes use of SELECT statement in LEFT JOIN that returns the records that have been downloaded.
Has anyone got anything more concise?
Cheers
/* ******* THE FOLLOWING REPLACES ALL PREVIOUS INSERT SCRIPTS ********* */
INSERT INTO #Table3 (DownloadHistoryID, DownloadID, SessionID, UserName, Downloaded)
(
SELECT
T2.DownloadHistoryID
, T1.DownloadID
, T1.SessionID
, T3.UserName
, CASE WHEN T3.downloadid IS NULL THEN 0 ELSE 1 END AS Downloaded
FROM #Table1 T1
LEFT JOIN #Table2 T2 --RETURNS ALL DATA FROM TABLE1
ON T1.DownloadID = T2.DownloadID
LEFT JOIN
(
-- RETURNS ONLY RECORDS THAT HAVE BEEN DOWNLOADED
SELECT T2.DownloadHistoryID, T2.DownloadID, T2.UserName, T2.Downloaded
FROM #Table2 T2
INNER JOIN #Table1 T1
ON T2.DownloadID = T1.DownloadID
WHERE T2.Downloaded <> 0
) T3
ON T2.DownloadHistoryID = T3.DownloadHistoryID
)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply