How to use pseudonyms in queries using Aliases along with Mysql?

Asked

Viewed 413 times

0

I would like to be able to use the Alias 'AS' along with mysql without having to do query alignment, that is, I want the secondary queries to be named to use them in the main query, it is possible?

For example, in the case below the AS is used nested form

Example 1 with consultations within consultations (and this way works)

SELECT ID FROM (
    SELECT ID, msisdn FROM (
        SELECT * FROM TT2
    ) AS T
) AS T

The way I’d like to do it would be without nesting, so:

Example 2 with external consultations in relation to the main consultation (thus doesn’t work)

SELECT name, LEFT(occupation,1) AS letra FROM OCCUPATIONS ORDER BY name AS tab1;

SELECT letra FROM tab1 ORDER BY letra ASC;

The main difference between example 1 and 2 is that in the first case the queries are nested within each other. The second consultation has sub-queries outside the main consultation.

The form 2 does not work, even using parentheses, so how to make use a pseudo name for the query to be represented temporarily with a "alias" nonnested form, this is possible?

mysql sql query
  • 2

    put an alias for a table (resulting from the query) only makes sense if you use this result in another query. There is no reason nor where this would be returned (thinking of rows and columns). That is, where this would be used tab1 ?

  • I forgot to put another query using the first as pseudo name, see if now got better

  • see if the answer works

  • and then young, solved ?

  • Worse than not, I threw the question at Soen and said it was not possible so far =\

  • I do not understand the down votes, the question is very clear and is part of the scope, this same question was asked on Soen and had 2 up votes, someone can suggest improvements to this doubt?

  • also do not see the pq do down, I put my up...rs but I believe that is already answered.

Show 2 more comments

1 answer

1


Now with the question edition, it became clearer. In this case, you can use the With:

With tab1 as (
SELECT name,LEFT(occupation,1) AS letra FROM OCCUPATIONS ORDER BY name
)

SELECT letra FROM tab1 ORDER BY letra ASC;

However, this clause is only available from version 8 of mysql. In previous versions, queries should be nested.

  • Apparently not, I’m testing on the hackerrank platform -> https://www.hackerrank.com/challenges/the-pads/problem

  • depends on the version of mysql, unless mistake is only available from version 8. before that, only nesting

Browser other questions tagged mysql sql query

You are not signed in. Login or sign up in order to post.