Help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with query

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-06-21 : 22:11:20

I have the following two queries and I need to combine somehow to select only the unique combos as shown in item returned in line 11 for the second query (maybe more than one combo). I cannot guarentee it will be the last line or max numbers.

SELECT UserID, GroupID From UserGroup

1 3
2 3
106 4
107 4
108 4
109 4
110 4
653 4
788 725
789 726

SELECT DISTINCT UserDetails.UserID, GroupDetails.GroupID
FROM UserDetails
INNER JOIN #t ON UserDetails.LoginName = #t.LoginName
INNER JOIN GroupDetails ON GroupDetails.GroupName = #t.GroupName

1 3
2 3
106 4
107 4
108 4
109 4
110 4
653 4
788 725
789 726
790 727

I thought this would work however it returns nothing

SELECT DISTINCT UserDetails.UserID, GroupDetails.GroupID
FROM UserDetails
INNER JOIN #t ON UserDetails.LoginName = #t.LoginName
INNER JOIN GroupDetails ON GroupDetails.GroupName = #t.GroupName
WHERE NOT EXISTS (SELECT UserID, GroupID From UserGroup)

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2009-06-21 : 22:42:09

Can you clarify a bit? I'm having trouble understanding what you're after. It might help to explain the business rules...

Cheers,

Tim

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-21 : 23:56:56

try this and check it once
SELECT DISTINCT UserDetails.UserID, GroupDetails.GroupID
FROM UserDetails
INNER JOIN #t ON UserDetails.LoginName = #t.LoginName
INNER JOIN GroupDetails ON GroupDetails.GroupName = #t.GroupName
WHERE NOT EXISTS (SELECT * From UserGroup where userid = userdetails.userid and groupid = groupdetails.groupid)

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-06-22 : 00:07:01

Thanks Tim,

Let me simplify my example here. #t, #y and #u represent my db while #i represents the import from the csv file. The idea is to:

do nothing if match from import to db,
update if details change on #t and #y,
insert if new user or group and link.

I have the update and insert working for user and group however I am having trouble linking the users and groups as the keys change when new rows inserted between the csv and db.

Create Table #t (UserID int, UserName nvarchar(50))

Insert into #t
(UserID, Name)
Select 1, Tim
Select 2, Steve
Select 3, John

Create Table #y (GroupID int, GroupName nvarchar(50))

Insert into #y
(GroupID, Name)
Select 10, Admin
Select 11, Installer
Select 12, Operator

Create Table #u (UserID int, GroupID int)

Insert into #u
(UserID, GroupID)
Select 1, 10
Select 1, 11
Select 2, 12
Select 3, 11

Create Table #i (UserID int, UserName nvarchar(50), GroupID int, GroupName nvarchar(50), UserID int, GroupID int)

Insert into #i
(UserID, UserName, GroupID, GroupName, UserID, GroupID)
Select 1, Tim, 10, Admin, 1, 10
Select 1, Tim, 11, Installer, 1, 11
Select 2, Steve, 12, Operator, 2, 12
Select 3, John, 11, Installer, 3, 11
Select 4, Adam, 20, CMS, 4, 20
Select 25, Mick, 10, Admin, 25, 10

The first three in #i are the same as #t, #y and #u and do not need to be updated or inserted.

On the 4th in #I we have a new user and group (adam, cms). So we can add the user and group to #t and #y then we need to link the two in #u. So #u should add the new userid and groupid from the new id’s in #t and #y.

On the 5th we have a new user linked to an existing group. So we can add the new user to #t. Next we need to link the two in #u. So #u should add the new userid and existing groupid from the id’s in #t and #y.

Output should be:

#t

(UserID, Name)
1, Tim
2, Steve
3, John
10, Adam
11, Mick

#y

(GroupID, Name)
10, Admin
11, Installer
12, Operator
15, CMS

#u

(UserID, GroupID)
1, 10
1, 11
2, 12
3, 11
10, 15
11, 10

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 11:29:47

ok. you can implement this by means of ssis package. what you need is a data flow task inside ssis which has following tasks.

1. a flat file source which connects to your csv to get source data
2. two lookup tasks with each of tables #t & #y to lookup based on user/group name and get back user/group id
3. each lookup task will be followed by a script task which calls a stored procedure if lookup yields no match. Inside SP you insert record to relevant (user/group) table and get back generated id as an output parameter to add to flow pipeline.
4. finally a oledb destination to populate all the values obtained from pipeline which is your desired result.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources