question

kavitha_sharma avatar image
kavitha_sharma asked

(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
selectlike
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
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: select 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. select 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. Oleg
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.