May 23, 2011 at 12:58 pm
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 23, 2011 at 1:27 pm
Please post some sample data and an example of the desired results.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 23, 2011 at 1:44 pm
Sorry, edited the original post with the info. Maybe those links should be sticky'd on top of the forum.
Also under the how to post data link, there is an error
http://www.sqlservercentral.com/articles/Best+Practices/61537/
The fourth block of code data has you doing SET IDENTITY_INSERT to ON 2x, instead of putting it back on OFF.
May 23, 2011 at 2:24 pm
If I understand this right, here's two methods for accomplishing this:
1:
SELECT PKeyItem,
ItemName,
IsUserItem = CASE WHEN EXISTS (SELECT * FROM UserItem WHERE FKeyItem = i.PKeyItem) THEN 1 ELSE 0 END,
IsGroupItem = CASE WHEN EXISTS (SELECT * FROM GroupItem WHERE FKeyItem = i.PKeyItem) THEN 1 ELSE 0 END
FROM Item i;
2.
SELECT PKeyItem,
ItemName,
IsUserItem = CASE WHEN ui.PKeyUserItem IS NOT NULL THEN 1 ELSE 0 END,
IsGroupItem = CASE WHEN gi.PKeyGroupItem IS NOT NULL THEN 1 ELSE 0 END
FROM Item i
LEFT JOIN UserItem ui
ON i.PKeyItem = ui.FKeyItem
LEFT JOIN GroupItem gi
ON i.PKeyItem = gi.FKeyItem;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 24, 2011 at 9:19 am
That doesn't seem to quite do it. When I run it using an fkeyuser flag like I outlined above I get:
1,first item,1,1
2,second item,1,1
3,third item,1,1
4,fourth item,1,1
I was expecting:
1,first item,0,1
2,second item,1,0
3,third item,0,0
4,fourth item,0,0
May 24, 2011 at 10:00 am
This should be better for you.
SELECT PKeyItem,
ItemName,
IsUserItem = CASE WHEN SS.itemuser = ua.pkeyuser THEN 1 ELSE 0 END,
IsGroupItem = CASE WHEN SS.GroupUser = ua.pkeyuser THEN 1 ELSE 0 END
FROM UserAccount UA
CROSS APPLY (
SELECT PKeyItem,ItemName
, ui.PKeyUserItem
,gi.PKeyGroupItem
,a.pkeyuser AS GroupUser
,ui.fkeyuser AS ItemUser
FROM Item i
LEFT OUTER JOIN GroupItem gi
ON i.PKeyItem = gi.FKeyItem
LEFT OUTER JOIN UserItem ui
ON i.PKeyItem = ui.FKeyItem
LEFT OUTER JOIN UserAccount A
ON gi.FKeyGroup = a.FKeyGroup
) SS
WHERE ua.pkeyuser = 1
ORDER BY PKeyItem
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 24, 2011 at 10:04 am
Seems to do the job, Thanks!
May 24, 2011 at 10:06 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply