May 23, 2011 at 12:38 pm
I'm having some troubles getting a query to work appropriately, and was hoping someone could assist. I have 3 tables: Item, UserItem, GroupItem. I'm basically trying to get every record in Item, and then add two columns isuseritem & isgroupitem (booleans) to the record set if they exist in UserItem or GroupItem for the specific keys associated with a user/group when they browse the list. Below is the table layout (abbrev).
CREATE TABLE Item
(
PKeyItem INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ItemName nvarchar(255) NOT NULL
);
CREATE TABLE UserItem
(
PKeyUserItem INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
FKeyItem int NOT NULL Default 0,
FKeyUser int NOT NULL Default 0
);
CREATE TABLE GroupItem
(
PKeyGroupItem INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
FKeyItem int NOT NULL Default 0,
FKeyGroup int NOT NULL Default 0
)
CREATE TABLE UserAccount
(
PKeyUser INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
FKeyGroup int NOT NULL Default 0,
UserName nvarchar(255)
)
CREATE TABLE Groups
(
PKeyGroup INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
GroupName nvarchar(255)
)
SET IDENTITY_INSERT Item ON
INSERT INTO Item (PKeyItem, ItemName)
SELECT '1', 'First Item' UNION ALL
SELECT '2', 'Second Item' UNION ALL
SELECT '3', 'Third Item' UNION ALL
SELECT '4', 'Fourth Item'
SET IDENTITY_INSERT Item OFF
SET IDENTITY_INSERT UserItem ON
INSERT INTO UserItem (PKeyUserItem, FKeyItem, FKeyUser)
SELECT '1', '2', '2' UNION ALL
SELECT '2', '3', '1' UNION ALL
SELECT '3', '1', '1' UNION ALL
SELECT '4', '4', '3'
SET IDENTITY_INSERT UserItem OFF
SET IDENTITY_INSERT GroupItem ON
INSERT INTO GroupItem (PKeyGroupItem, FKeyItem, FKeyGroup)
SELECT '1', '2', '2' UNION ALL
SELECT '2', '3', '2' UNION ALL
SELECT '3', '1', '1' UNION ALL
SELECT '4', '4', '3'
SET IDENTITY_INSERT GroupItem OFF
SET IDENTITY_INSERT UserAccount ON
INSERT INTO UserAccount (PKeyUser, FKeyGroup, UserName)
SELECT '1', '1', 'First User' UNION ALL
SELECT '2', '2', 'Second User' UNION ALL
SELECT '3', '3', 'Third User'
SET IDENTITY_INSERT UserAccount OFF
SET IDENTITY_INSERT Groups ON
INSERT INTO Groups (PKeyGroup, GroupName)
SELECT '1', 'First Group' UNION ALL
SELECT '2', 'Second Group' UNION ALL
SELECT '3', 'Third Group'
SET IDENTITY_INSERT Groups OFF
So if pkeyuser 1 was browsing I would want the recordset to have isusergroup boolean set to 1 on pkeyitem 2 and 0 on the other 3 rows. Also isgroupitem would be set to 1 for PKeyItem 1 only, the rest to 0's. That make sense now?
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 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply