getting fully qualified column names (mysql)

metaperl has asked for the wisdom of the Perl Monks concerning the following question:

I think this bug sums up my quandry perfectly:

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

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' ) )
[download]

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.

Comment on getting fully qualified column names (mysql) Download Code

Replies are listed 'Best First'.
Re: getting fully qualified column names (mysql) by perrin (Chancellor) on Feb 17, 2009 at 19:28 UTC
This has nothing to do with perl, but it's time for you to learn about column aliases in SQL. `SELECT foo AS bar...` [download]	[reply] [d/l]
Re: getting fully qualified column names (mysql) by jhourcle (Prior) on Feb 18, 2009 at 02:10 UTC
I'd argue that 'fully qualified' is a misnomer in databases. What should be sent when your 'table' is really a view? Do you send the underlying table, or the view's information? What do we send for calculated fields? What about database links (for those databases that support 'em)? I'd agree with perrin -- just use column aliases, and you'll be fine. Of course, depending on what's actually in your database, I foresee problems unless there's a UNIQUE constraint on `work_phone(people_id,phone_type)`. (ie, there's only one work / fax / whatever type of number per person). If there isn't this constraint, it might be easier to not do as many table joins, and just handle the assignment of the phone type in logic once you've retrieved the logic. If there is the constraint, consider using a view to handle the denormalization (and hardcode the phone_types).	[reply] [d/l]