sql - mysql syntax explanation

Question

I would like to know what the a.*, c.name, ... a.access etc means. In other words, what exactly am I referring to when I add a letter before the dot and the funciton of the dot.

Here is an example of code where I found this occurrence:

$query = "SELECT a.*, c.name as categoryname,c.id as categoryid, ".
         "c.alias as categoryalias, c.params as categoryparams".
        " FROM #__k2_items as a".
        " LEFT JOIN #__k2_categories c ON c.id = a.catid";

        $query .= " WHERE a.published = 1"
        ." AND a.access <= {$aid}"
        ." AND a.trash = 0"
        ." AND c.published = 1"
        ." AND c.access <= {$aid}"
        ." AND c.trash = 0"
        ;

Answer 1

If you look at the FROM clause, you see this:

FROM #__k2_items as a

And in the LEFT JOIN clause, you see this:

LEFT JOIN #__k2_categories c ON c.id = a.catid

That aliases the #__k2_items table (whatever it's really called) to the name a, and #__k2_categories to c, respectively. In this case it's just to save typing and improve the readability of the query, really.

The dot associates a column name to a table name so MySQL knows which tables to look in, in case you have columns of the same name in more than one table involved in the query. That is, it resolves column ambiguity.