Please 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 2008 Forums

Transact-SQL (2008)

Please help with query

Author

Topic

rama108
Posting Yak Master

115 Posts

Posted - 2012-03-09 : 11:06:25

Hi All,
I need help with the following query:

LinksTable looks like this:

LinkId Name
-------------
5070 myapp
5071 myapp
5073 myapp
1 another app
3 anotherapp

xRef table looks like this:

EmployeeId LinkId LinkName
-----------------------
2 4 anotherapp
7 6 anotherapp
8 5 anotherapp

I need to add entiries into the xRef table as:

EmployeeId LinkId LinkName
-----------------------
2 5070 myapp
7 5071 myapp
8 5073 myapp

In other words, take employeeid from xRef table where LinkName = 'anotherapp'
and insert records into xRef with that employeeid and get the linkId and LinkName from
Linkstable and insert it into xRef along with the employeeid.

How do I do that?

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-03-09 : 11:21:40

Do you want to add a record into your xRef table for each employee and each record in your LinksTable?

rama108
Posting Yak Master

115 Posts

Posted - 2012-03-09 : 11:29:42

I want to add record into xRef taking employeeid from xRef and taking linkid and linkname where linkname = 'myapp'.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 11:33:07

how do you determine which EmployeeId should be linked to which LinkId ?
in your output you show it as 2 5070,7 5071 etc but there should be a criteria based on which it should link. It cant rely upon order you see in select results as there's no concept of order in sql table. However if you've another unique valued column (may be an id column of audit column) you can specify order based on that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rama108
Posting Yak Master

115 Posts

Posted - 2012-03-09 : 11:41:45

Can this give you an idea?

insert into xref
select x.emplyeeId, l.LinkId
From xref x
join linkstable l
on x.linkid = l.linkid

But this is not working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 11:58:28

still you've not given response to my order criteria part. can you tell based on what order you want values to be linked. Just to reiterate,you dont have concept of order in sql table unless you specify it by means of ORDER BY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rama108
Posting Yak Master

115 Posts

Posted - 2012-03-09 : 11:59:31

Order does not matter.

Thanks for your time

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-03-09 : 12:03:17

Using your data example:
How would you want to determine that EmployeeId 2 should be matched to LinkId 5070?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 12:06:54

[code]
insert into xref
select t2.employeeId, t1.LinkId
from (select *,row_number() over (order by LinkId) as rn from LinksTable where Name='myapp')t1
inner join (select *,row_number() over (order by EmployeeId) as rn from xRef where LinkName='anotherapp') t2
on t2.rn=t1.rn
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rama108
Posting Yak Master

115 Posts

Posted - 2012-03-09 : 12:12:52

xRef table does not have a linkname or name column

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 12:50:15

quote:
Originally posted by rama108

xRef table does not have a linkname or name column

but your posted sample data shows this

xRef table looks like this:

EmployeeId LinkId LinkName
-----------------------
2 4 anotherapp
7 6 anotherapp
8 5 anotherapp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rama108
Posting Yak Master

115 Posts

Posted - 2012-03-09 : 15:08:42

Thank you so much Vishakh. Your query helped me tremendously. I just modified a little and it worked perfectly. Thanks again for your time.

Rama

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 21:43:35

no probs
Glad that I could help you sort the issue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources