CODEWITHSUNDEEP
mysqlsql
Jason
Jason

Reputation: 1307

Is it possible to select from an alias in mysql?

I created a table alias named tbl, and I want to select from that. But I am unable to do this. I know that my code is not correct nor optimized, but I am simply testing the MySQL CASE.

select
case
when exists (select username from tbl) then 'Username Exists'
else 'Username does not exist'
end
from (select 1 as id, 'bob' as username, 'pass' as password) as tbl

I get the error: Table 'users.tbl' doesn't exist in database users.

Upvotes: 0

Views: 85

Answers (2)

Alberto Solano
Alberto Solano

Reputation: 8227

You have that error because there's no physical table involved in the query, because tbl is just the alias you created. If you want just to test if your username exists, execute this query:

SELECT CASE
WHEN id = 1 THEN 'Username Exists' ELSE 'Username does not exist'
END
FROM (SELECT 1 AS id, 'bob' AS username, 'pass' AS password) AS tbl

Upvotes: 3

echo_Me
echo_Me

Reputation: 37243

try this out

select
     case
         when exists (select username from tbl where  username = 'bob' and  password = 'pass') then 'Username Exists'
     else 'Username does not exist'
     end as existanse_column
from  tbl
limit 1

DEMO HERE

Upvotes: 1

Related Questions