Naming columns from other tables

manpreet Best Answer 2 years ago

Just a quick question about naming columns that come from other tables, below i have the tables put in the SQL statement but after it I put an abbreviated version "MO" is this correct/ will this work in all situations or should i just stick to the full version like module.mod_code?

SELECT MO.MOD_CODE, MO.MOD_NAME, MO.ECTS_UNITS,MO.DESCRIPTION
FROM MODULE MO, SYLLABUS SY, PROGRAMME PR
WHERE MO.MOD_CODE = SY.MOD_CODE
AND SY.PROG_CODE = PR.PRO_CODE
AND PR.NFQ_LEVEL = ‘LEVEL 9’
AND MO.DESCRIPTION LIKE ‘%RESEARCH%’ OR DESCRIPTION LIKE ‘%QUALATIVE%’ OR DESCRIPTION LIKE     ‘%QUANTITATIVE%’;

Thanks :)

manpreet 2 years ago

 You cannot stick to the full version. Once you have given a table or subquery an alias, that is the name of that object in the scope of the query. Actually, what happens is that the table name becomesthe table alias, so you can use it for qualifying columns in the table.

SELECT MO.MOD_CODE, MO.MOD_NAME, MO.ECTS_UNITS,MO.DESCRIPTION
FROM MODULE MO JOIN
     SYLLABUS SY
     ON MO.MOD_CODE = SY.MOD_CODE JOIN
     PROGRAMME PR
     ON SY.PROG_CODE = PR.PRO_CODE
WHERE PR.NFQ_LEVEL = 'LEVEL 9' AND
      (MO.DESCRIPTION LIKE '%RESEARCH%' OR
       MO.DESCRIPTION LIKE '%QUALATIVE%' OR
       MO.DESCRIPTION LIKE '%QUANTITATIVE%'
      );