(select displayname from employee e where e.employee=a.author) as Author what does this statement mean

Need the meaning of the code and if I want to extract only one specific author how should I write it

more ▼

asked Apr 13 at 07:08 AM in Default

avatar image


(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The code in the title of this question is not complete, it is just a fragment of the query which most probably selects some records from the table aliased as a and also includes the column named displayname from the other table named employee. This column is then aliased (given a name) as Author. The method used to select the data in this way is not necessarily most optimal because the same may be achieved via JOIN. After making the whole bunch of assumptions, it is possible to try to complete the script after filling in all the missing pieces:

     a.*, (select displayname from employee e where e.employee = a.author) Author
     from authors a
     -- filtering for specific Author value is not possible because
     -- the column is not available to the WHERE clause

The biggest problem with this query is that filtering for a specific displayname (aliased as Author) is not possible, the column is not available to the where clause. For example, suppose that it is known that the name of one specific author is Bozo, The Clown. Try to add a line reading WHERE Author = 'Bozo, The Clown' to the script, the script will error out. This hints that the way the query is written is not only sub-optimal from performance perspective, it is also inferior because it is not trivial to filter the data for a specific display name. One could argue that it is possible to add the where clause directly to the select statement in parentheses, but this will not work because the outer select will still select ALL ROWS from the author table with value of NULL for Author field for all rows except one.

Consider restating this query as a join. This way, filtering is easily possible, i.e.

     a.*, e.displayname Author
     from authors a inner join employee e
         on e.employee = a.author
     -- where e.displayname = 'Bozo, The Clown';

With the join, filtering is possible. Now, uncommenting the last line will cause the query to return just one record for a specific author.

Hope this helps.


more ▼

answered Apr 13 at 02:33 PM

avatar image

20.6k 3 7 29

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 13 at 07:08 AM

Seen: 22 times

Last Updated: Apr 13 at 02:33 PM

Copyright 2018 Redgate Software. Privacy Policy