Hi,

I have some tables representing rooms, doors and keys. Each key opens one room.

Some rooms have more than one door. So the depencencies are like this:

key <-1-n-> door <-1-1-> room

There is a view for the keys:

CREATE VIEW v_keys AS SELECT ... FROM keys;

Now I would like to add a column "room" to this view, but without changing the number of rows!
At this point, I would like to assume that all the doors a key may open, belong to the same room. So it would be ok, to just fetch the first room...

How can I add this to the view?

Thanks
Magnus

this?

CREATE VIEW v_keys AS
SELECT ..
FROM keys k
INNER JOIN (SELECT *,
ROW_NUMBER() OVER (PARTITION BY Room ORDER BY Room) AS Seq
FROM Rooms) r
On r.Key = k.Key
AND r.Seq = 1

I've just given some indicative column names above so make sure you use correct column names instead

based on what you said, you have rooms table and doors table - a room can have multiple doors and keys table - one for each room.

 what is you ddl structure - a since a key can open multiple doors that belong to a same room --does the key table have foreign key reference to the room table or do you have it for doors table(as hirearchy model)

if you have rooms as FK in keys table

select distinct keyno,roomno from keys inner join rooms on keys.rid=rooms.rid

if you have hierachy model

select distinct keyno,roomno from keys inner join doors on keys.doorid=doors.doorid

inner join rooms on rooms.rid=doors.rid

Or

SELECT <columns>,(SELECT TOP 1 room FROM Keys K WHERE K.PK=Keys.PK ORDER BY...) room

FROM keys

Hello and thanks for the replies!

I have done it with a subquery, like Uri suggested. Because it always delivers exactly one value, even if there is no room (the value is then null).

And because Visakh's suggestion contained SQL keywords I never heared about (partition etc.) Sounds interesting...

Thanks
Magnus