Using Aliases in SQL

Question

I use the following code, but I am trying to understand Aliases, I get an error with this code "SQL command not properly ended", line 2

SELECT student.lastname, student.firstname
FROM student AS Student_Name
INNER JOIN memberof ON (Student_Name.SID = memberof.studentid)
INNER JOIN studentgroup ON (memberof.groupid = studentgroup.gid)
GROUP BY Student_Name.lastname , Student_Name.firstname 
HAVING COUNT(memberof.groupid) >= 2

If you are using an alias, use it always: `SELECT Student_Name.lastname, Student_Name.firstname FROM student AS Student_Name ...` — juergen d, Apr 24 '13 at 00:05
@ChaosPandion: No. All selected columns are also in the group by clause — juergen d, Apr 24 '13 at 00:10
Down voted for not using a tag to help define context - for example letting us know that this was for Oracle — Andre, Apr 24 '13 at 01:18

score 1 · Accepted Answer · answered Apr 24 '13 at 00:17

I'm thinking that you are using Oracle which apparently does not support the as keyword for table aliases.

SELECT s.lastname, 
       s.firstname
FROM student s
INNER JOIN memberof 
    ON s.SID = memberof.studentid
INNER JOIN studentgroup 
    ON memberof.groupid = studentgroup.gid
GROUP BY s.lastname, 
         s.firstname 
HAVING COUNT(*) >= 2;

score 0 · Answer 2 · edited May 23 '17 at 12:12

0

MYSQL:

If you use an alias you need to always refer to that table using its alias. Genrally I use aliases to shorten my names so that I don't have to type as much

SELECT s.lastname, s.firstname
FROM student AS s 
INNER JOIN memberof ON (s.SID = memberof.studentid)
INNER JOIN studentgroup ON (memberof.groupid = studentgroup.gid)
GROUP BY s.lastname , s.firstname 
HAVING COUNT(memberof.groupid) >= 2

"s" may be too short depening on the context of your code , but you get the idea

ORACLE

Oracle does not support AS for table aliases, only for column aliases : SQL Command not properly ended?

edited May 23 '17 at 12:12

Community

1
1

answered Apr 24 '13 at 00:08

Andre

2,449
25
24

Most of my aliases are 1-3 characters in length - as long as the aliases are localized (which they should all be), but I find such names appropriate. The biggest key is to be consistent with naming. (In which case `Student_Name` is awful reads as a scalar and not a relation.) – user2246674 Apr 24 '13 at 00:10
Same error: "SQL command not properly ended" – user12074577 Apr 24 '13 at 00:10
check your line breaks make sure you have spaces ie put it all on one line and make sure there is a space after "s" – Andre Apr 24 '13 at 00:12

Using Aliases in SQL

2 Answers2