Professional Web Applications Themes

Splitting data from multiple tables - MySQL

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, ...

  1. #1

    Default Splitting data from multiple tables

    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 ?

    rick@fourfront.ltd.uk Guest

  2. #2

    Default Re: Splitting data from multiple tables

    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)


    Paul Guest

  3. #3

    Default Re: Splitting data from multiple tables

    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 ?

    rick@fourfront.ltd.uk Guest

  4. #4

    Default Re: Splitting data from multiple tables

    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
    ==================
    Jerry Guest

Similar Threads

  1. Replies: 2
    Last Post: June 7th, 07:27 AM
  2. DB design...multiple tables for ancillary data or just one?
    By darrel in forum Dreamweaver AppDev
    Replies: 0
    Last Post: March 21st, 05:24 PM
  3. Can one <form> submit data to multiple tables?
    By jip in forum Dreamweaver AppDev
    Replies: 3
    Last Post: March 3rd, 09:11 PM
  4. Creating tables and merging them for multiple data
    By Agin in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 03:39 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139