CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
serhio
serhio

Reputation: 28586

Operate pairs from 2 tables in SQL

Say I have the following situation: I have some cities with buildings. Each city is divided into districts. Each district should have a "main house" (one per district).

I have (sql server 2005) a corresponding associate table CITY_BUILDS (IDCITY, IDBUILD)
I have also a buildings table BUILD (ID, DISTRICT, IS_MAIN, COLOR)

So, say the city mayor decided to repaint the "main house" of the districts in the color of the first house in the district list (crazy idea, but anyway)

I need to in a city foreach district select the "main house", then search for the first district house, peek its color and paint the main house.

I started a stub for my procedure but soon got confused...

DECLARE L_CURSOR CURSOR FAST_FORWARD FOR
SELECT B.DISTRICT, B.IS_MAIN, B.ID 
FROM BUILD B 
    INNER JOIN CITY_BUILDS C_B 
        ON B.ID = C_B.IDBUILD                   
WHERE IDCITY = 142 --AND B.IS_MAIN=1
ORDER BY DISTRICT

PS. By FIRST building, I mean ANY first occurrence in the CITY_BUILDS list... Also, that "FIRST" house should not be the MainHouse itself...

PPS. I used a cursor, because once I obtain the pair (ID_MAIN_HOUSE - ID_FIRST_HOUSE) in the real project I need to do call a stored procedure with that 2 arguments and the COLOR...

Upvotes: 1

Views: 86

Answers (1)

Preet Sangha
Preet Sangha

Reputation: 65546

Assuming IS_MAIN is 1 for the main district I think you can do all of them with one update statement

UPDATE B
SET COLOR = M.COLOUR
FROM CITY_BUILDS C  -- get city
-- get main
INNER JOIN BUILD M ON M.ID = C.IDBUILD AND M.IS_MAIN =1
 -- get others join them to city and main district
INNER JOIN BUILD B ON C.ID = C.IDBUILD AND C.IS_MAIN <> 1 AND M.DISTRICT = B.DISTRICT 
-- if you really only need one city then uncomment following 
--WHERE C.IDCITY = 142

Upvotes: 1

Related Questions