Need help to form SQl query

Question

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 2-Oct-12 19:37pm

SruthiR

Updated 2-Oct-12 23:49pm

v3

Add a Solution

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

2 solutions

Solution 2

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

Posted 3-Oct-12 2:38am

Vijay Walunj,Navi mumbai

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.

Add a Solution

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

damodara naidu betha · Accepted Answer · 2012-10-03T02:21:00

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

Need help to form SQl query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Need help to form SQl query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us