CASE result set problems

[Muddy_Funster]

Hey, I am trying to plug into a third party database, and am having problems getting my CASE statement to work.

 

I have been at this for a while now, even tried using a WITH and didn't get anywhere.  Basicly I am looking for a result set that will ahve the "Comment" set to yes or no depending on if the sheetID is in both the Files table and the tbl_dialog table.  Problem is, that while the CASE is outputing Yes and No appropriately for different sheetID's it is also causing 4700 records to be returned for each sheetID and taking an age to perform the querie.

 

Hope someone can help, here's the SQL in it's latest incarnation:

SELECT
Files.SheetID, Files.PenName, Files.FileDate, Files.FileName, 
Users.UserName, 
Folders.FolderName,
'Comment' = CASE WHEN ((SELECT Files.sheetID) IN (SELECT Files.SheetID FROM dbo1.Files INNER JOIN dbo2.tbl_dialog ON (dbo1.Files.SheetID = dbo2.tbl_dialog.sheetID))) 
THEN 'Yes' 
ELSE 'No' 
END 

FROM 
dbo1.Files AS Files 
INNER JOIN dbo1.Sheet AS Sheet 
ON (Files.SheetID = Sheet.SheetID)
LEFT JOIN dbo1.Pad AS Pads
ON (Sheet.PadID = Pads.PadID) 
LEFT JOIN UserFolders AS Folders
ON (Files.AssignedFolderID = Folders.FolderID)
RIGHT JOIN dbo1.Users AS Users
ON (Users.UserID = Folders.UserID)
, dbo2.tbl_dialog


WHERE Pads.PadPrefix = 'String' 
AND (FileStatus <> 1 AND Files.SheetID > 100000) 

GROUP BY Files.SheetID, Files.FileStatus, Files.PenName, Files.FileDate, Files.FileName, 
Users.UserName, 
Folders.FolderName, 
dbo2.tbl_dialog.sheetID

ORDER BY Files.SheetID DESC

 

Some data has been manipulated, but the structure is intact.

[kickstart]

Hi

 

Basic problem seems to be that you are doing a JOIN onto tbl_dialog without specifying on what columns. Bet that table has the same number of rows as you are getting duplicates on sheetIDs. Also the GROUP BY columns do not seem to match those that are selected. Also think you can use a JOIN rather than the subselect (I am assuming that tbl_dialog with have multiple rows for each sheet ID)

 

My SQL server is a bit rusty and nothing handy to try it out on, but hopefully this will give you an idea.

 

SELECT Files.SheetID, Files.PenName, Files.FileDate, Files.FileName, Users.UserName, Folders.FolderName,
CASE WHEN dialog.SheetID IS NULL THEN 'No' ELSE 'Yes' END AS Comment
FROM dbo1.Files AS Files 
INNER JOIN dbo1.Sheet AS Sheet ON (Files.SheetID = Sheet.SheetID)
LEFT JOIN dbo1.Pad AS Pads ON (Sheet.PadID = Pads.PadID) 
LEFT JOIN UserFolders AS Folders ON (Files.AssignedFolderID = Folders.FolderID)
RIGHT JOIN dbo1.Users AS Users ON (Users.UserID = Folders.UserID)
LEFT OUTER JOIN (SELECT DISTINCT dialog.SheetID FROM dbo2.tbl_dialog) dialog ON (Files.SheetID = dialog.SheetID)
WHERE Pads.PadPrefix = 'String' 
AND (FileStatus <> 1 AND Files.SheetID > 100000) 
ORDER BY Files.SheetID DESC

 

All the best

 

Keith

[Muddy_Funster]

Hey Keith, many thanks for taking a look.  I'll give it a try when I'm back in the office tomorrow.  But there are over 9400 rows in tbl_dialog ( I did a SELECT COUNT(*) from the table when I got the random 4700 results per sheet returning as I instantly thought that it was looping the select from that table for every sheet ID) although I had thought that linking the JOIN on the sheetID field within the case would have been enough.  I did try a LEFT JOIN at one point, but it only returned the sheets that had comments on them, although I can't remember if I tried it in conjunction with the CASE or not.

[Muddy_Funster]

Keith, you're a STAR! worked perfectly.

 

Although I'm still not sure where the 4700 was coming from, so I'm going to spend a couple hours working through the code to see if I can see what exactly I did wrong.  After that It's onto the real fun stuff of pulling the user name of the last comment posted from a cross ref table on user ID only when the CASE returns 'Yes'. 

 

Anyone know if you can nest CASE statements? 

[kickstart]

Hi

 

Suspect the GROUP BY had eliminated some of the duplicates.

 

I would assume you can nest CASE statements, same as you can nest IF statement in MySQL.

 

All the best

 

Keith