May 17, 2021 at 6:48 pm
Hello All,
I am new to the SQL world and with work I use SQL studio.
I have a question, I want to inner join 3 tables.
the table names are Integration.Encounter, Integration.Schedule and Integration.Person.
I have selected the specific columns I want and I coded it like this.
select top 5*
,e.[Person ID]
,e.[Facility]
,p.[Gender]
,p.[Race]
,s.[Appointment Type]
,s.[No Show Appointment]
from...
The final part of the coding chunk I am having trouble with. I am not sure how to join everything together.
You will see I classified the column pulls by e, s, and p.
schedule table = s
person table = p
encounter table = e
all tables share a similar person ID.
all the help is greatly appreciated.
thank you!
May 17, 2021 at 9:23 pm
Since you have all the hard work done, all that's left is to put the table names in and define how you want to join them. Since you have a column to join by (person ID), it should be something as easy as:
FROM [Schedule] AS
JOIN [Person] AS [p] ON.[person ID] = [p].[person ID]
JOIN [Encounter] AS [e] ON.[person ID] = [e].[person ID]
Personally though, I would avoid single-character aliases. They work in cases like this OK, but as you work with more and more complex queries, you will find that single character aliases result in a lot of scrolling to determine what they mean. In this case, it is easy to tell what s, p, and e stand for, but what if instead of person, the table was called "employee"? You now have 2 tables that start with e.
I actually have it in my coding standards (for both .NET development and TSQL development) to not allow single character aliases and variable names.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 17, 2021 at 9:49 pm
Just avoiding single aliases as a rule doesn't keep developers from using aliases like a1, a2, etc. When there are just a few joins, I've never understood the panic over single-character aliases. A glance at the joins tells you what tables b.somecolumn and b2.othercolumn belong to.
Although we don't shun single-character aliases, we make it a practice to use the same alias for a given table whenever possible. It makes reading queries more intuitive, but not crowded.
For example:
i = Inventory
s = Sales
sa = SalesArchive
p = people
pro = process
pri = priority
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 17, 2021 at 10:18 pm
The actual rule we have in place isn't specific to single character aliases though. Our rule is that aliases and variable names must be specific enough to identify the object. So if the stored procedure was "RO_GetSalesData" and there was an alias of "SO", I would know that that is for the Sales Order table. But if it was called "RO_GetEmployeeData" and the alias was "SO", I'd need documentation on what that stands for or I am digging through the code and cursing the original developer under my breath (who MAY even be me) as I have no idea how "SO" relates to an Employee or the employee related data.
It isn't that there is a panic over single-character aliases, it is more for habit forming purposes. It is much easier to form a habit of well-named aliases and variables than it is to argue about when it is appropriate and inappropriate to use poorly named aliases and variables or to have exceptions.
It just becomes challenging trying to define the line where a single character alias is acceptable and where it is problematic. If you have standards like i is for Inventory and will always be for Inventory, then I see nothing wrong with it, but you need to document that those are the acceptable single character aliases. If you don't, how will a new developer know what it means? If I were to start at your company and you gave me a script to optimize that used Inventory, Sales, and SalesArchive and it was lengthy (100+ lines of code) and I see aliases in there like "s" and "sa" and "i", I would have no idea what those stood for. To make it worse, if you had an ERP system that managed that that used strangely named objects like IV00101, I may still be at a loss as to what "s" actually stands for. The amount of time a new developer to your system spends trying to decode that "s" means "sales" could have MUCH better been used working on tuning the code. Now, if it is in the coding documentation that an S alias means Sales and will only mean Sales in all places where it is used, then as a new developer on your system, I know that s is sales. But if that is not documented, I may waste a lot of time as a new developer trying to decode your stored procedure.
Alternately, if I use poorly named aliases but the code works great, I may not need to go back and modify it for 5 or more years. Looking at ANY code I wrote 5 years ago makes me cringe, but even more so when I am scrolling through it trying to map a column alias to a table when it has names like "s" or "m1". It usually makes me cringe though because I did something silly that at the time made sense to me.
From a .NET standpoint, it is considered bad form to use single character variables and I find that when re-evaluating old code, single character variables usually don't make sense to what they are being used for; especially class or global variables. With class or global variables, I like to include the datatype in the variable name so I don't need to do a whole bunch of scrolling to make sure it is being used appropriately. That one isn't a rule in our coding standards, but is a habit I like to have.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 17, 2021 at 10:47 pm
I've always liked the idea of assigning a unique standard alias for every table and always using it. You can add a 1, 2, etc., if you need to use the same table multiple times in the same query.
For example, the orders table is always ord, customers is always cus, etc.. Only one place I've worked has ever allowed me to enforce that rule, but it made code so much easier to read. You instantly and always knew what table was being referenced (although, yes, some of the later abbreviations were not really directly related to the table, since many abbrevs had already been taken).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 18, 2021 at 5:51 am
This was removed by the editor as SPAM
May 18, 2021 at 4:23 pm
I've always liked the idea of assigning a unique standard alias for every table and always using it. You can add a 1, 2, etc., if you need to use the same table multiple times in the same query.
For example, the orders table is always ord, customers is always cus, etc.. Only one place I've worked has ever allowed me to enforce that rule, but it made code so much easier to read. You instantly and always knew what table was being referenced (although, yes, some of the later abbreviations were not really directly related to the table, since many abbrevs had already been taken).
I agree that a standard for aliasing tables is useful - but I have run into too many situations where a single table needs to be aliased differently depending on its usage.
For example - a Person table will be aliased differently depending on how it is used in that query. It could be used to reference the patient, the user who performed the action, the user who recorded the action, the provider, approving provider, billing provider, referring provider, performing provider, task owner, task creator - and many, many more.
Using per, per1, per2, per3, per4 for the person table will be more confusing than specific aliases for each specific use.
I don't find it very difficult to identify what object an alias references - and it is easy enough to split the query window to view the FROM portion and the SELECT portion in separate windows. However - if the code uses 'generic' aliases or serial aliases (per1, per2, etc.) then it becomes more difficult to remember while reviewing the code.
What I really have a problem with is when assigning and uses aliases is done haphazardly - where in a single query someone uses an alias for one table and doesn't use an alias on another table, or where the columns don't use the table aliases. Or when the alias is assigned as the full table name or a 10 character alias (why?).
Of course - it also helps if the code is at least somewhat formatted in a readable fashion.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 18, 2021 at 4:40 pm
I sometimes do use the table name as the alias. The reason I do this is that SQL Prompt SQL Format will automatically add the alias onto my columns IF the alias exists. I have not found an option in SQL Format to add the table name to the columns if no alias exists. Sometimes, the tables are named exactly how I would name the alias such as "Location" or "Item".
But that is just me. I am not saying my approach is the best or best practice; it is just my habit. I picked that up from doing application development and I'd have poorly named variables (like A) which made sense while I was coding things up, but come back to it in a year and I am scrolling all over the place trying to figure out what the heck an "A" is. The exception to that in my coding is SOMETIMES I will use "X", "Y", and "Z" to indicate a coordinate, but then I'll usually have an object with an X, Y, and Z property on it, but if I need to keep track of the previous X value of that object, I MAY store point.X in a variable called X. USUALLY, I will call it something like "prevX" so I know that it is the previous X value, or currX to indicate current X value, but I've been known to call things "X" before.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 18, 2021 at 4:46 pm
ScottPletcher wrote:I've always liked the idea of assigning a unique standard alias for every table and always using it. You can add a 1, 2, etc., if you need to use the same table multiple times in the same query.
For example, the orders table is always ord, customers is always cus, etc.. Only one place I've worked has ever allowed me to enforce that rule, but it made code so much easier to read. You instantly and always knew what table was being referenced (although, yes, some of the later abbreviations were not really directly related to the table, since many abbrevs had already been taken).
I agree that a standard for aliasing tables is useful - but I have run into too many situations where a single table needs to be aliased differently depending on its usage.
For example - a Person table will be aliased differently depending on how it is used in that query. It could be used to reference the patient, the user who performed the action, the user who recorded the action, the provider, approving provider, billing provider, referring provider, performing provider, task owner, task creator - and many, many more.
Using per, per1, per2, per3, per4 for the person table will be more confusing than specific aliases for each specific use.
I don't find it very difficult to identify what object an alias references - and it is easy enough to split the query window to view the FROM portion and the SELECT portion in separate windows. However - if the code uses 'generic' aliases or serial aliases (per1, per2, etc.) then it becomes more difficult to remember while reviewing the code.
What I really have a problem with is when assigning and uses aliases is done haphazardly - where in a single query someone uses an alias for one table and doesn't use an alias on another table, or where the columns don't use the table aliases. Or when the alias is assigned as the full table name or a 10 character alias (why?).
Of course - it also helps if the code is at least somewhat formatted in a readable fashion.
In those cases, I used aliases like:
per_patient, per_action, per_recorder, etc.
But the alias must start with the standard alias followed by an underscore.
As I said, only one place ever let me have this rule and enforce it. Developers hated it, since they had to look up the alias to use, but naturally it made all code consistent, which to me was well worth looking up the alias names when needed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 19, 2021 at 11:39 am
A couple of points on this.
First, in the suggested solutions, you left off the schema from the tables. Always include the schema in tables referenced in the FROM criteria. It actually reduces the overhead in query optimization by the tiniest amounts. This is because it means the optimizer doesn't have to search the default schema first to find the table. Tiny little thing, but every little bit adds up. As someone said about backpacking, ounces make pounds.
Second, code is communication. The more obscure we make our code with weird references or patterns that make it hard to interpret or understand, the more obscure that communication. I have long argued in favor of clarity in code. So a table with the name, Address, can have an alias of 'a'. Whereas, the AddressCore table (or whatever) would be 'ac'. Generally, I follow the approach that each time you use a given table, you use the same alias. Further, that you keep it simple as above. However, as with any rule, there are tons of exceptions. For example, let's say the table was AddressSystem. Well, an alias of 'as' might just cause a few issues depending on where you use it (and if you want to avoid brackets, which I do). So, maybe 'ay' instead. But still, the idea is always going to be to attempt to be as clear as possible. The more your code is like a foreign language where you have to go run it through Google translate or look up stuff in dictionaries, the harder that code will be to deal with. The more time you're spending interpreting code, the less you're spending generating more code or tuning the existing code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply