Need the meaning of the code and if I want to extract only one specific author how should I write it
asked Apr 13 at 07:08 AM in Default
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:
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.
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.
answered Apr 13 at 02:33 PM