I created select where I join tables related to tblDevices table
select
DeviceID
, DeviceType+' '+Brand+ ' '+Model as Device
from tblDevices d
left join tblBrands b
on d.BrandID=b.BrandID
left join tblDeviceTypes dt
on d.DeviceTypeID=dt.DeviceTypeID
left join tblModels m
on d.ModelID=m.ModelID
And also created select to join tables related to tblLocations table
select
LocationID
, Facility + ' ' + Building + ' ' + Wing + cast(Floor as varchar(10)) + '-' + Room as Location
from tblLocations l
left join tblFacilities fa
on l.FacilityID=fa.FacilityID
left join tblBuildings b
on l.BuildingID=b.BuildingID
left join tblWings w
on l.WingID=w.WingID
left join tblFloors fl
on l.FloorID=fl.FloorID
I would like to create select statement to join tblRequest table with previous two select statements and get approach such select statement:
select
RequestID
, RequestDate
, Department
, FirstName
, LastName
, TicketNo
, Amount
, DeviceType+' '+Brand+ ' '+Model as Device
, Facility + ' ' + Building + ' ' + Wing + cast(Floor as varchar(10)) + '-' + Room as Location
from tblRequests r
left join ....
I will appreciate if someone will show how to solve the problem.
You can do this. You've basically already done the whole shebang.
As you proceed below, make sure that you assign aliases to each table and use the aliases in the joins and in the eventual SELECT list of columns to be returned.
Start the FROM clause with tblRequests.
Join tblLocations to tblRequests
Join the other 4 related tables to that.
Join tblDevices to tbleRequests
Join the other 4 related tables to that.
Then, build your SELECT list using the aliases from those tables.
Try it. You'll see. You're better at this than you think.
Hi JeffModen. Thanks for reply.
What do you think? If create select statement by this way:
SELECT
RequestID
, RequestDate
, Department
, FirstName
, LastName
, TicketNo
, Amount
, DeviceType +' '+ Brand + ' '+ Model as Device
, Facility + ' ' + Building + ' ' + Wing + cast(Floor as varchar(10)) + '-' + Room as Location
from tblRequests r
left join (
SELECT
DeviceID
, DeviceType
, Brand
, Model
from tblDevices d
left join tblBrands b
on d.BrandID=b.BrandID
left join tblDeviceTypes dt
on d.DeviceTypeID=dt.DeviceTypeID
left join tblModels m
on d.ModelID=m.ModelID) as d
on r.DeviceID=d.DeviceID
left join (
SELECT
LocationID
, Facility
, Building
, Wing
, Floor
, Room
from tblLocations l
left join tblFacilities fa
on l.FacilityID=fa.FacilityID
left join tblBuildings b
on l.BuildingID=b.BuildingID
left join tblWings w
on l.WingID=w.WingID
left join tblFloors fl
on l.FloorID=fl.FloorID) as l
on r.LocationID=l.LocationID
That should work nicely and the cool part about that is that it's going to be wicked easy for anyone to maintain. I would, however, follow the strict rule of always using table aliases and that true even with the final outer select. You have no table aliases there.
I'm also a little (not much) concerned about the LEFT OUTER JOINS everywhere. You appear to have good DRI (Declared Referential Integrity) as seen in the ERD in your original post and should be able to get away with only inner joins if you have NOT NULL constrainsts in all the involved (join) columns. The LEFT OUTER JOINS also won't hurt anything here if you do so kind of a moot point.
Depending on what you want, they could be a problem if you don't have the correct NOT NULL constraints. "It Depends". Only testing against actual requirements will tell there.
I'll also tell you, in the same breath, that such outer joins have operated better for me for certain things in the past even where they are not necessary. They have the added bonus of indicating a mental ERD to someone not familiar with the actual ERD or the tables/data.
You could also turn each derived table into a separate CTE each. Some people prefer that kind of a "Top Down" approach. That's what I normally use but the way you have it laid out is crystal clear.
There will be some that suggest that it could be all in a single query. While I agree that it could, I don't think that's a wise move. The code is logically divided "on paper" for human readability and "sectional testing" the way you have it and that kind of maintainable "sectional testing" ability is a rare thing is the world of coding.
Over all, I'll say "Nicely Done". You do need to add the missing aliases and it would be helpful to other people if you added a comment describing the overall purpose of each of the derived tables you've created. Something like "This gets all of the related LOCATION information" and "This gets all of the related DEVICE information". While I agree that's obvious if you read the code, it's really nice to have something even more obvious that will very quickly direct someone to the correct section of code for troubleshooting or for MACs (Moves, Adds, and Changes).
In other words, I follow the mantra of "Make it work, make it fast, make it pretty... and it ain't done 'til it's pretty".
In my case only the Device group tables not allow NULL value. Just one question. You say about using table aliases. I though that all my tables have aliases. If I'm wrong. Can you show about what aliases you mean?
Thanks.
Look at the columns in your SELECTs. The column names all need to have aliases added to them even if they're distinct to all other columns in the whole query. The reason for this is that a person that is troubleshooting or doing MACs on the code could have zero familiarity with the tables. If the columns are aliased with the table aliases, they won't need such esoteric knowledge and won't need to view the table schema to find which columns belong to which tables.
You did fine in the ON conditions because you were forced to alias the column names due to the non-distinct column names you were joining on. Such aliasing must occur in the SELECT list. Of course, if the column is in the outer final SELECT then, in this case, you would use the derived table alias that you created. But all the columns in all the SELECT need to have the appropriate table or derived table name as a part of the column usage.
Yep... in the case, the code will certainly work without them but, again, you can't be just a master of joining things to be a masterr at writing code. Consider the next person that has to read your code (which might even be you in just several months). Make it easy for them to determine which columns come from which tables by aliasing the columns anywhere and everywhere in the code. If frequently do the same even if the code contains just one table because you never know when you'll need to add another table.
If you "get into the habit", it'll become an automatic thing and you'll never have to spend much time at all to do it. It's truly a matter of good form that will never be a wasted effort.
Thank you for the feedback and thanks for trying on your own. It's not my job to judge but I have to say that it's been a real pleasure working with you and you've definitely got the right stuff. I was impressed from the moment I saw the ERD ;you were working with and the code you tried. Well done!
