CODEWITHSUNDEEP
mysqlalias
PiGamma
PiGamma

Reputation: 157

Alias not working in query

I need to display the name of the user and their department who has raised the maximum number of queries. I wrote the following query by joining 5 tables; user, query, profile, degree, and department.

The problem is in the result the alias is not being used for the column names. For both the columns it appears as just name

select user.name 'USER_NAME',department.name 'DEPT_NAME'
from user
inner join query on (query.user_id=user.id)
inner join profile on (user.profile_id=profile.id)
inner join degree on (profile.degree_id=degree.id)
inner join department on (degree.department_id=department.id)
group by user.name
order by count(query.id) desc
limit  1

Upvotes: 3

Views: 3316

Answers (4)

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4201

Try it this way;

select thisuser.name 'USER_NAME',department.name 'DEPT_NAME'
from user as  thisuser
inner join query on (query.user_id=thisuser.id)
inner join profile on (thisuser.profile_id=profile.id)
inner join degree on (profile.degree_id=degree.id)
inner join department on (degree.department_id=department.id)
group by thisuser.name
order by count(query.id) desc
limit  1

but this is not a good practice when constructing a query; it looks like this

select thisuser.name,thisdept.name from
(select name,user_id from user) as thisuser
inner join
(select name,user_id from department) as thisdept
on thisuser.user_id = thisdept.user_id

Upvotes: 1

worldofjr
worldofjr

Reputation: 3886

Simply remove the quotes from your query and it will work, but you could use the keyword AS to make it clear that you're using an alias.

I would also use lower case aliases in order to avoid confusion with keywords, and also use uppercase for keywords;

SELECT user.name AS user_name, department.name AS dept_name
...

Upvotes: 0

M.Be
M.Be

Reputation: 322

The following syntax work perfectly for me : 

select U.name AS "USER_NAME", D.name AS "DEPT_NAME"
from user U
inner join query Q on (Q.user_id=U.id)
inner join profile P on (U.profile_id=P.id)
inner join degree C on (P.degree_id=C.id)
inner join department D on (C.department_id=D.id)
group by U.name
order by count(Q.id) desc
limit  1;

Sometimes, Mysql prefer you pout an alias on your column when you use some JOIN.

Upvotes: 1

Sagar
Sagar

Reputation: 647

Use 'as' keyword in order to use alias name in mysql and also remove single quotes.

select user.name as USER_NAME from user;

Upvotes: 2

Related Questions