Splitting data from multiple tables - MySQL

[rick@fourfront.ltd.uk]

How can I split the data from multiple tables in the return from MySQL
in this example;-

tablea columns = ( uid, a, b, c )
tableb columns = ( uid, d, e, f )

I can get all the related columns like so

select * from tablea left join tableb on tableb.uid=tablea.uid ;

This gives me all the columns but it is not possible to tell from the
resulting associative output which of the tables the columns come
from.

i.e.
uid, a, b, c, uid, d, e, f
1,100, 101,102,1,103,104,105

What I would rather see returned is

tablea.uid, tablea.a, tablea.b, tablea.c, tableb.uid, tableb.d,
tableb.e, tableb.f
1,100,101,102,1,103,104,105

Then the process which has made the query knows exactly which table
each of the figures came from, and can deal with the data accordingly.

Is this possible ?

[Paul]

ltd.uk wrote: 

Yep, alias them:

SELECT
`a`.`uid` `tablea_uid`,
`a`.`a` `tablea_a`,
`a`.`b` `tablea_b`,
`a`.`c` `tablea_c`,
`b`.`d` `tableb_d`,
`b`.`e` `tableb_e`,
`b`.`f` `tableb_f`
FROM `tablea` `a`
LEFT JOIN `tableb` `b` USING(`uid`) ;

(there is no need for tableb.uid as it is always the same as the equivalent
field from tablea)

[rick@fourfront.ltd.uk]

On 28 Mar, 22:19, "Paul Lautman" <com> wrote: 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>
> Yep, alias them:
>
> SELECT
> `a`.`uid` `tablea_uid`,
> `a`.`a` `tablea_a`,
> `a`.`b` `tablea_b`,
> `a`.`c` `tablea_c`,
> `b`.`d` `tableb_d`,
> `b`.`e` `tableb_e`,
> `b`.`f` `tableb_f`
> FROM `tablea` `a`
> LEFT JOIN `tableb` `b` USING(`uid`) ;
>
> (there is no need for tableb.uid as it is always the same as the equivalent
> field from tablea)- Hide quoted text -
>
> - Show quoted text -[/ref]

No - can't do that - the whole point is that the tables have columns
which are modified by a 3rd party app.
The process which is reading the tables doesn't know the names of the
columns and it would be too inefficient to look them all up each time
a query is made (there are a large number of queries per second from
mulitple processes).
All I want is for the query itself to return the name of the table as
well as the name of the column.
Any ideas ?

[Jerry]

ltd.uk wrote: 
>> Yep, alias them:
>>
>> SELECT
>> `a`.`uid` `tablea_uid`,
>> `a`.`a` `tablea_a`,
>> `a`.`b` `tablea_b`,
>> `a`.`c` `tablea_c`,
>> `b`.`d` `tableb_d`,
>> `b`.`e` `tableb_e`,
>> `b`.`f` `tableb_f`
>> FROM `tablea` `a`
>> LEFT JOIN `tableb` `b` USING(`uid`) ;
>>
>> (there is no need for tableb.uid as it is always the same as the equivalent
>> field from tablea)- Hide quoted text -
>>
>> - Show quoted text -[/ref]
>
> No - can't do that - the whole point is that the tables have columns
> which are modified by a 3rd party app.[/ref]

A very poor implementation! Apps should not be changing table structure
on the fly.
 

No, it's not. If you're changing the table structure on the fly, you'll
have to look up the structures every time you make a query.

It's one of the penalties of having a bad implementation.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
net
==================