-
Aug 7th, 2020, 06:39 AM
#1
Thread Starter
Fanatic Member
Simple alias query
I have this query on SQL SERVER:
Code:
SELECT costcentres.ID, costcentres.centrename, supervisors.username as supervisorname FROM costcentres LEFT JOIN users as supervisors on supervisors.id=costcentres.supervisorno WHERE supervisors.supervisorname IS NULL ORDER BY 1 DESC
I gives an error and says that there is no column with the name of supervisorname.
Why would that be or what is the correct syntax if I wish to use an alias for a table name and for the name of one of its fields?
Thanks
PK
-
Aug 7th, 2020, 06:56 AM
#2
Re: Simple alias query
This is how I would write the query:
Code:
SELECT
CC.ID
,CC.centrename
,U.username as supervisorname
FROM costcentres CC
LEFT JOIN users U
on U.id= CC.supervisorno
WHERE U.supervisorname IS NULL
ORDER BY CC.ID DESC;
Alias the table names CostCenters as CC users as U, the order by should use the column names not a number, and yes that does work and is valid SQL but it is better to use actual column names rather than number position in the query
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Aug 7th, 2020, 07:07 AM
#3
Thread Starter
Fanatic Member
Re: Simple alias query
Thanks Gary,
Your query gives me the same error.
I am sure my field and table names are correct.
PK
-
Aug 7th, 2020, 07:26 AM
#4
Re: Simple alias query
It can't be correct post the structure of the 2 tables. This is what I do for a living (writing complicated SQL for developers, and this one is simple)
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Aug 7th, 2020, 07:52 AM
#5
Re: Simple alias query
Try replacing this
Code:
WHERE supervisors.supervisorname
with this
Code:
WHERE supervisors.username
-
Aug 7th, 2020, 10:04 AM
#6
Hyperactive Member
Re: Simple alias query
SQL Server isn't as nice about aliasing as some other database languages.
So if you think "I entered supervisors.username as supervisorname, so now I can use the shorter name all the time", nope, it's not true. You mostly have to use the full table.column, computed field, or whatever you put before the "as" keyword in the rest of your query.
-
Aug 7th, 2020, 10:39 AM
#7
Thread Starter
Fanatic Member
Re: Simple alias query
Thanks gary,
I know you are an expert from your previous advice to me, and I appreciate your help..
Here are my two tables:
Here is my full query which I simplified for the post: (|Also for ahenry's and dee-u's information)
Code:
Query = "Select costcentres.ID, projects.projectname, costcentres.centrename, costcentres.latlong, itemcats.catname, ccbudgets.budgetname, 0, costcentres.budget, 0, format(startdate,'dd-MMM-yy'), format(enddate,'dd-MMM-yy'), format(startedon,'dd-MMM-yy'), format(completedon, 'dd-MMM-yy'), supervisors.username as supervisorname, supervisors.telcell as supervisortelcell, inspectors.username as inspectorname, inspectors.telcell as inspectortelcell, places.placename, places.latlong, costcentres.markupfraction, costcentres.planlink, costcentres.approved, costcentres.approvedby, costcentres.closed, costcentres.addedby from (((((costcentres left join projects on projects.id=costcentres.projectno) left join ccbudgets on ccbudgets.id=costcentres.budgetcentre) left join itemcats on itemcats.id =costcentres.itemscat) left join users as supervisors on supervisors.id=costcentres.supervisorno) left join users as inspectors on inspectors.id=costcentres.inspectorno) left join places on places.id=costcentres.deliverto"
Thanks
PK
Last edited by Peekay; Aug 7th, 2020 at 12:06 PM.
-
Aug 7th, 2020, 11:02 AM
#8
Hyperactive Member
Re: Simple alias query
I'm not sure what was supposed to be in the attachments, but they didn't come through. Also your query doesn't have a where clause, so i doubt that it is describing the original problem.
Like everyone has said, WHERE supervisors.supervisorname IS NULL will not work if supervisorname is just an alias.
Edit: Table aliases in the from clause are actually useful in SQL Server, which is probably what gary was concentrating on. Using "as" in your select clause is basically just to give the resultset column a useful name - it doesn't help in the rest of the query.
Edit #2: I learned something new today - resultset aliases can at least be used in the ORDER BY clause in SQL Server 2008 and higher. And of course they are useful in some cases like nested sub-queries.
Last edited by ahenry; Aug 7th, 2020 at 11:46 AM.
-
Aug 7th, 2020, 12:09 PM
#9
Re: Simple alias query
Originally Posted by ahenry
I'm not sure what was supposed to be in the attachments, but they didn't come through. Also your query doesn't have a where clause, so i doubt that it is describing the original problem.
Like everyone has said, WHERE supervisors.supervisorname IS NULL will not work if supervisorname is just an alias.
Edit: Table aliases in the from clause are actually useful in SQL Server, which is probably what gary was concentrating on. Using "as" in your select clause is basically just to give the resultset column a useful name - it doesn't help in the rest of the query.
Edit #2: I learned something new today - resultset aliases can at least be used in the ORDER BY clause in SQL Server 2008 and higher. And of course they are useful in some cases like nested sub-queries.
You can use column aliases in an ORDER BY but not in a WHERE because of execution order. The WHERE filter happens early, before the results is filled, so it doesn't know what the alias is. Same is true for using aliases in joins... doesn't work (or it shouldn't ... not able to test that at the moment) But, the ORDER by happens once all the data has been retrieved... and the alias known... so then it can can be used at that point.
Table aliasing happens right up front though... so you can use that right away.
-tg
-
Aug 7th, 2020, 01:39 PM
#10
Re: Simple alias query
As you can see in the image there is nothing named supervisorname it ushould be = U.username
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Aug 7th, 2020, 02:13 PM
#11
Thread Starter
Fanatic Member
Re: Simple alias query
I thank all you guys for your inputs.
Although I understand the arguments, I am still not sure where I can use aliases and where not, especially in this case where I have to link the users table twice with different join criteria.
Would it be possible to illustrate a simple join for me based on one field from the costcentres table joining two different users from the users table?
Thanks
PK
-
Aug 7th, 2020, 03:07 PM
#12
Re: Simple alias query
It basically boils down to this:
For Column aliases:
Where you can use the alias:
* in the select
* in the order by
Where you cannot use the alias:
* in the where clause
* in the join clause
For table aliases:
Where you can use the alias:
* Everywhere
-tg
-
Aug 7th, 2020, 03:17 PM
#13
Hyperactive Member
Re: Simple alias query
Originally Posted by techgnome
It basically boils down to this:
For Column aliases:
Where you can use the alias:
* in the select
* in the order by
Not quite true, you can't use column aliases in the select, you can only define them there. But table aliasing is fine anywhere, and that's all Peekay needs:
Totally untested:
Code:
SELECT CC.ID, CC.centrename,
supervisors.username as supervisorname, inspectors.username as inspectorname
FROM costcentres CC
LEFT JOIN users inspectors on inspectors.id= CC.inspectorno
LEFT JOIN users supervisors on supervisors.id= CC.supervisorno
ORDER BY CC.ID DESC;
This is all fine pretty much as you had it, just make sure that your WHERE clause is filtering on supervisors.username or inspectors.username, not on the aliases.
Note that the "as supervisorname / as inspectorname" aliases are not needed for the SQL to work at all. They are purely there for the convenience of the code (or person) that is looking at your resultset.
Last edited by ahenry; Aug 7th, 2020 at 03:29 PM.
-
Aug 8th, 2020, 01:21 AM
#14
Thread Starter
Fanatic Member
Re: Simple alias query
Thanking all you guys who gave me this masterclass. Something I should have had twenty years ago!
PK
-
Aug 9th, 2020, 04:20 AM
#15
Re: Simple alias query
Again not completely true with column alias, you cannot use column alias in a group by clause (need actual column names). Not sure on rolliup clause as I don't normally use them
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Aug 9th, 2020, 05:09 AM
#16
Re: Simple alias query
Originally Posted by GaryMazzone
Not sure on rolliup clause as I don't normally use them
You cannot use column aliases anywhere except in ORDER BY clause.
cheers,
</wqw>
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|