Define Column Aliases?

Question

I have a query like this:

SELECT `*` 
  FROM (`threads` t, `members` m) 
 WHERE `m`.`id` = t.author 
   AND `t`.`type` = '0' 
   AND `t`.`category` = '1'

And basically what happens is that there is an ID field in both tables (members and threads) so what's happening is that the results array is getting messed up. IE: There is only one ID field which is being populated from the members table.

What I need to do is make the results with a prefix infront of their key name so I can distinguish between the two: IE: Add 't.' to all thread fields and 'm.' to all members fields.

So results should be like: m.id = x, t.id = y Instead, results at the moment are like: id = x (the id field from the thread table is completely overwritten by the one from the members table)

Answer 1

You can set an alias for columns that have the same name:

SELECT t.ID as threadsID, m.ID as membersID, <...> WHERE `m`.`id` = t.author AND `t`.`type` = '0' AND `t`.`category` = '1'

Answer 2

You could do it by naming each column one by one:

SELECT m.`id` AS "m_id", `t`.`id` AS "t_id", t.`username` AS "t_username"
FROM (`threads` t, `members` m) 
WHERE `m`.`id` = t.author 
   AND `t`.`type` = '0' 
   AND `t`.`category` = '1'

Answer 3

Use:

SELECT m.id AS x,
       t.id 'y' 
  FROM MEMBERS m
  JOIN THREADS t ON t.id = m.author
                AND t.type = '0'
                AND t.category = '1'

Column aliases are defined on a column by column basis - you can't use wildcards/etc.
You can use the AS keyword, or simply enclose the column alias within single quotes if the column name doesn't contain special characters - use double quotes if it does. You can combine single/double quote usage with the AS keyword.

Do I have to List all the Columns?

---

Yes, you have to list all the columns unless you like duplicate columns because you choose to use m.* or t.*. There is no convention in SQL that supports what you ask. SELECT * is not an ideal practice - read this answer for details beyond this situation why.

Addendum

---

I took the liberty of rewriting your query to use ANSI-92 JOIN syntax - your example used ANSI-89. There's no performance difference.

Answer 4

If you just want the id's to be separate, you can do

SELECT `*`, t.id as t_id, m.id as m_id
  FROM (`threads` t, `members` m) 
WHERE `m`.`id` = t.author 
  AND `t`.`type` = '0' 
  AND `t`.`category` = '1'

As the others said, it's a column alias.