I think this bug <http://bugs.mysql.com/bug.php?id=27262> sums up my
quandry perfectly:

<BLOCKQUOTE>
It makes no sense that I can use fully qualified names in the query,
but results can not
be returned fully qualified as well.
</BLOCKQUOTE>

I have a situation where I need to select from the same table twice -
I need to get the work and fax phone numbers for a person.

Now I can use table aliases to make each query of the phone table
distinct (one such call shown:

SELECT *
FROM agents
INNER JOIN people ON ( people.id = agents.people_id )
INNER JOIN phone AS work_phone ON ( work_phone.people_id = people.id
AND work_phone.phone_type = (
SELECT id
FROM phone_types
WHERE enum_name = 'Work' ) )

but there is no way to get back the results with the column names
table-qualified.

So I am going to be forced to make the queries for phone one at a time
because there will be a column name conflict when I use the same table
to get the fax phone in addition to the work phone...

metaperl wrote on 17.02.2009 20:26:
No you are not, stop using SELECT * and give each column an alias

SELECT work_phone.phone_number as work_phone,
home_phone.phone_number as home_phone
FROM agents
INNER JOIN people ON ( people.id = agents.people_id )
INNER JOIN phone AS work_phone ON ( work_phone.people_id = people.id AND
work_phone.phone_type = 1)
INNER JOIN phone AS home_phone ON ( home_phone.people_id = people.id AND
work_phone.phone_type = 2)

(I simplified the condition on the phone_type)

Thomas

On 2009-02-17, metaperl <metaperl (AT) gmail (DOT) com> wrote:

select foo.bar as "foo.bar" from foo;

the above works in postgres 8.3, and may work in other systems too.

you just need column aliases... which means not using select * but
naming (and aliasing where apropriate) the columns that interest you.

On Feb 17, 2:40*pm, Thomas Kellerer <FJIFALSDG... (AT) spammotel (DOT) com>
wrote:

spammotel - lol

yes, using VIEWS makes this process much simpler.

amazing what a night's sleep will do for you.