Multi join issue

Question

*EDIT** Thanks for all the input, and sorry for late reply. I have been away during the weekend without access to internet. I realized from the answers that I needed to provide more information, so people could understand the problem more throughly so here it comes:

I am migrating an old database design to a new design. The old one is a mess and very confusing ( I haven't been involved in the old design ). I've attached a picture of the relevent part of the old design below:

The table called Item will exist in the new design as well, and it got all columns that I need in the new design as well except one and it is here my problem begin. I need the column which I named 'neededProp' to be associated( with associated I mean like a column in the new Item table in the new design) with each new migrated row from Item.

So for a particular eid in table Environment there can be n entries in table Item. The "corresponding" set exists in table Room. The only way to know which rows that are associated in Item and Room are with the help of the columns "itemId" and "objectId" in the respective table. So for example for a particular eid there might be 100 entries in Item and Room and their "itemId" and "objectId" can be values from 1 to 100, so that column is only unique for a particular eid ( or baseSeq which it is called in table BaseFile).

Basically you can say that the tables Environment and BaseFile reminds of each other and the tables Item and Room reminds of each other. The difference is that some tables lack some columns and other may have some extra. I have no idea why it is designed like this from the beginning.

My question is if someone can help me with creating a query so that I can be able to find out the proper "neededProp" for each row in the Item-table so I can get that data into the new design?

*OLD-PART**This might be a trivial question but I can't get it to work as I want. I want to join a few tables as in the sql-statement below. If I start like this and run this query

select * from Environment e 
  join items ei on e.eid = ei.eid

I get like 400000 rows which is what I want. However if I add one more line so it looks like this:

select * from Environment e 
  join items ei on e.eid= ei.eid
  left  join Room r on e.roomnr = r.roomobjectnr

I get an insane amount of rows so there must be some multiplication going on. I want to get the same amount of rows ( like 400000 in this case ) even after joining the third table. Is that possible somehow? Maybe like creating a temporary view with the first 2 rows.

I am using MSSQL server.

Answer 1

So without knowing what data you have in your second query it's very difficult to say exactly how to write this out, and you're likely having a problem where there's an additional column that you are joining to in Rooms that perhaps you have forgotten such as something indicating a facility or hallway perhaps where you have multiple 'Room 1' entries as an example.

However, to answer your question regarding another way to write this out without using a temp table I've crufted up the below as an example of using a common table expression which will only return one record per source row.

;WITH cte_EnvironmentItems AS (
    SELECT *
    FROM Environment E
    INNER JOIN Items I ON I.eid = E.eid
), cte_RankedRoom AS (
    SELECT *
        ,ROW_NUMBER() OVER (ORDER BY R.UpdateDate DESC) [RN]
    FROM Room R
)

SELECT *
FROM cte_EnvironmentItems E
LEFT JOIN cte_RankedRoom R ON E.roomnr = R.roomobjectnr
    AND R.RN = 1

Answer 2

btw,do you want column from room table.if no then

select * from Environment e 
  join items ei on e.eid= ei.eid
  where  e.roomnr in (select r.roomobjectnr from Room r )

else

select * from Environment e 
  join items ei on e.eid= ei.eid
  left  join (select distinct roomobjectnr from Room) r on e.roomnr = r.roomobjectnr