Click here to Skip to main content
14,977,142 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all,

I have 3 tables in sql server namely Owner, Asset and AssetOwner.
Owner:-

 Owner,    Domain, Position, Project,  ID
1. Alex  - ABC  -   0     -  X1   -   111
2. Peter - PQR  -   1     -  X2   -   222
3. Sam   - GHI  -   2     -  X3   -   333 
4. Alex  - LMN  -   3     -  X4   -   111
    
Asset :-

  Asset,   Position,   Center
1. ABC   - 0        -  Ivert 
2. PQR   - 1        -  SGYt
3. GHI   - 2        -  RenGT
4. LMN   - 3        -  IUOy

AssetOwner:- 
    
   Owner,     Asset,   Center
1. Alex   -   ABC     
2. Peter  -   PQR     
3. Sam    -   GHI     
4. Alex   -   LMN

Using the first 2 tables data(Asset and Owner table), I need to fill the value for column 3 -Center in AssetOwner table using below steps.

Find owner of a domain in Owner table. Check if any of the domain of an owner has same ID.
if yes (rows 1 & 4), take the first corresponding row (row 1) and get the value of Position(col 3). In Asset table, retrieve value of Center for the corresponding Position. Update value of Center in AssetOwner Table for the corresponding owner.

If no(rows 2 & 3), take ID column for Project ( col 4) get the value of Position(col 3). In Asset table, retrieve value of Center for the corresponding Position. Update value of Center in AssetOwner Table for the corresponding owner.

Please help me form the below query. Will a help be of any help

Thanks in advance.
Posted
Updated 2-Oct-12 23:49pm
v3
Comments
Abhijit Parab 3-Oct-12 1:49am
   
Is it possible to post output format which will be more helpful
SruthiR 3-Oct-12 1:54am
   
Output is nothing but updating the values of column Center in AssetOwner table using the above rules.

AssetOwner table should look like this.

Owner, Asset, Center
1. Alex - ABC -Ivert
2. Peter - PQR -SGYt
3. Sam - GHI -RenGT
4. Alex - LMN -IUOy
Devang Vaja 3-Oct-12 6:09am
   
You have said that
if yes (rows 1 & 4), take the first corresponding row (row 1) and get the value of Position(col 3).
so o/p should be
1. Alex - ABC -Ivert 2. Peter - PQR -SGYt 3. Sam - GHI -RenGT 4. Alex - LMN -Ivert
if we take TOP 1 ROW...
Divya RS 3-Oct-12 8:19am
   
You are going to update by what basis, based on domain or owner, your output and stated explanation differs. Pls review it once again

Hi Shruti ..

Try the following code block

SQL
 WITH OACTE AS (
  SELECT O.Owner,O.Domain,A.Center,O.Id,ROW_NUMBER()
  OVER (PARTITION BY O.Owner,O.Id ORDER BY O.Id) ROWNUM
  FROM Owner O JOIN Asset A ON O.Domain = A.Asset
)

SELECT Owner,Domain,CASE WHEN ROWNUM > 1 THEN
 (SELECT TOP 1 Center FROM OACTE WHERE Owner = OA.Owner AND Id = OA.ID)
 ELSE OA.Center END As Center  FROM OACTE As OA


Thank you
   
Comments
fjdiewornncalwe 3-Oct-12 10:17am
   
My vote of 1. Please educate the OP on homework, do not just give him a cut/paste solution as he will not learn anything.
try this

SQL
update ao
set
ao.Center=a.center
from AssetOwner ao
join asset a1 on a1.asset=ao.Asset
join owner o1 on o1.Owner=ao.owner and a1.Asset=o1.Domain
join owner o2 on o1.owner=o2.owner and o1.Id=o2.Id and  o1.Owner=ao.owner
join asset a on a.possition=o1.position
where o1.domain=(select top 1 domain from  owner where id=o1.id and owner=o1.Owner)



i hope this will work for you
   
Comments
fjdiewornncalwe 3-Oct-12 10:17am
   
My vote of 1. Please educate the OP on homework, do not just give him a cut/paste solution as he will not learn anything.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900