Results 1 to 16 of 16

Thread: Simple alias query

  1. #1

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    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

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    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

  3. #3

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: Simple alias query

    Thanks Gary,

    Your query gives me the same error.
    I am sure my field and table names are correct.

    PK

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    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

  5. #5
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Simple alias query

    Try replacing this
    Code:
    WHERE supervisors.supervisorname
    with this
    Code:
    WHERE supervisors.username
    Regards,

    â„¢

    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  6. #6
    Hyperactive Member
    Join Date
    Jan 2018
    Posts
    264

    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.

  7. #7

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    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:
    Name:  SQLTableCostCentres.jpg
Views: 221
Size:  85.4 KB
    Name:  SQLTable Users.jpg
Views: 248
Size:  22.1 KB

    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.

  8. #8
    Hyperactive Member
    Join Date
    Jan 2018
    Posts
    264

    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.

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Simple alias query

    Quote Originally Posted by ahenry View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    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

  11. #11

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    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

  12. #12
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13
    Hyperactive Member
    Join Date
    Jan 2018
    Posts
    264

    Re: Simple alias query

    Quote Originally Posted by techgnome View Post
    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.

  14. #14

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: Simple alias query

    Thanking all you guys who gave me this masterclass. Something I should have had twenty years ago!

    PK

  15. #15
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    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

  16. #16
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Simple alias query

    Quote Originally Posted by GaryMazzone View Post
    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
  •  



Click Here to Expand Forum to Full Width