Professional Web Applications Themes

Need help with a sql-statement - MySQL

Hello, I need help with a sql-statement. I have the following tables: pers_tab pers_id|name .................... 1 | Meier 2 | Müller 3 | Huber 4 | Schmitt tel_tab pers_id|tel ........................ 1 | 7654654 1 | 456546 1 | 5465465 2 | 54656 2 | 5646 The result should be: pers_id|name |tel1 |tel2 |tel3 | tel(n) .............................................. 1 | Meier | 7654654 | 456546 | 5465465 |... 2 | Müller | 54656 | 5646 | null |... 3 | Huber | null | null | null 4 | Schmitt | null | null | null Thanks, Markus...

  1. #1

    Default Need help with a sql-statement

    Hello,

    I need help with a sql-statement.
    I have the following tables:

    pers_tab

    pers_id|name
    ....................
    1 | Meier
    2 | Müller
    3 | Huber
    4 | Schmitt

    tel_tab

    pers_id|tel
    ........................
    1 | 7654654
    1 | 456546
    1 | 5465465
    2 | 54656
    2 | 5646

    The result should be:

    pers_id|name |tel1 |tel2 |tel3 | tel(n)
    ..............................................
    1 | Meier | 7654654 | 456546 | 5465465 |...
    2 | Müller | 54656 | 5646 | null |...
    3 | Huber | null | null | null
    4 | Schmitt | null | null | null

    Thanks,
    Markus

    markus43534 Guest

  2. #2

    Default Re: Need help with a sql-statement


    markus43534 wrote:
     

    Getting all the telephone numbers in the same row as the pers_id and
    name is easy with the GROUP_CONCAT function.
    However, this will put all the phone numbers into the third column. It
    is not possible to construct a query with a variable amount of results
    columns, where the number of potential columns are not known before the
    query is executed.
    It is also not possible to have a query return 2 rows containing 5
    columns and other rows containing more than 5 columns.

    So, if a result like:

    pers_id|name |tels
    ..............................................
    1 | Meier | 7654654, 456546, 5465465,...
    2 | Müller | 54656, 5646
    3 | Huber | null
    4 | Schmitt | null

    is acceptable, then a left join and use of GROUP_CONCAT should work
    fine. If not then I'm afraid you've dipped.

    SELECT
    p.pers_id, p.name, group_concat( t.tel )
    FROM `pers_tab` p
    LEFT JOIN `tel_tab` t USING ( `pers_id` )
    GROUP BY 1, 2

    Captain Guest

  3. #3

    Default Re: Need help with a sql-statement


    markus43534 wrote:
     

    I think this is a crosstab problem. To complete it properly (to deal
    with n numbers), I think you'd need a stored procedure - something I'm
    not at all familiar with. Roland Bouman describes the solution he
    provides as not 'hard at all, just a bit tedious':
    http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html

    In the comments at the bottom of his page are some links to more
    generic solutions - including my favourite, pasting the data into excel
    - although if it was me, I'd probably try to find a php solution to the
    problem of representing the results.

    strawberry Guest

  4. #4

    Default Re: Need help with a sql-statement


    strawberry wrote:
     
    >
    > I think this is a crosstab problem. To complete it properly (to deal
    > with n numbers), I think you'd need a stored procedure - something I'm
    > not at all familiar with. Roland Bouman describes the solution he
    > provides as not 'hard at all, just a bit tedious':
    > http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html
    >
    > In the comments at the bottom of his page are some links to more
    > generic solutions - including my favourite, pasting the data into excel
    > - although if it was me, I'd probably try to find a php solution to the
    > problem of representing the results.[/ref]

    Otherwise, you can also construct a query along these lines (although
    there's probably a more elegant way of writing this):

    SELECT DISTINCT(pers_id), tel1,tel2,tel3
    FROM
    (SELECT t1.pers_id,t1.tel tel1,t2.tel tel2, t3.tel tel3
    FROM tel_tab t1
    LEFT JOIN tel_tab t2 ON t1.pers_id = t2.pers_id
    AND t2.id != t1.id
    LEFT JOIN tel_tab t3 ON t2.pers_id = t3.pers_id
    AND t2.id != t3.id
    AND t3.id != t1.id)x
    GROUP BY pers_id;

    Note that this is only part of the query. It would need to be LEFT
    JOINed onto a list of names

    strawberry Guest

  5. #5

    Default Re: Need help with a sql-statement


    strawberry wrote:
     
    > >
    > > I think this is a crosstab problem. To complete it properly (to deal
    > > with n numbers), I think you'd need a stored procedure - something I'm
    > > not at all familiar with. Roland Bouman describes the solution he
    > > provides as not 'hard at all, just a bit tedious':
    > > http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html
    > >
    > > In the comments at the bottom of his page are some links to more
    > > generic solutions - including my favourite, pasting the data into excel
    > > - although if it was me, I'd probably try to find a php solution to the
    > > problem of representing the results.[/ref]
    >
    > Otherwise, you can also construct a query along these lines (although
    > there's probably a more elegant way of writing this):
    >
    > SELECT DISTINCT(pers_id), tel1,tel2,tel3
    > FROM
    > (SELECT t1.pers_id,t1.tel tel1,t2.tel tel2, t3.tel tel3
    > FROM tel_tab t1
    > LEFT JOIN tel_tab t2 ON t1.pers_id = t2.pers_id
    > AND t2.id != t1.id
    > LEFT JOIN tel_tab t3 ON t2.pers_id = t3.pers_id
    > AND t2.id != t3.id
    > AND t3.id != t1.id)x
    > GROUP BY pers_id;
    >
    > Note that this is only part of the query. It would need to be LEFT
    > JOINed onto a list of names[/ref]

    But that only gets up to 3 telephone numbers

    Captain Guest

  6. #6

    Default Re: Need help with a sql-statement


    strawberry wrote:
     
    >
    > I think this is a crosstab problem. To complete it properly (to deal
    > with n numbers), I think you'd need a stored procedure - something I'm
    > not at all familiar with. Roland Bouman describes the solution he
    > provides as not 'hard at all, just a bit tedious':
    > http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html
    >
    > In the comments at the bottom of his page are some links to more
    > generic solutions - including my favourite, pasting the data into excel
    > - although if it was me, I'd probably try to find a php solution to the
    > problem of representing the results.[/ref]

    That's pretty neat. Does all the OP asked for except for the varying
    number of columns in the records, which I'm sure is nto possible.

    Captain Guest

  7. #7

    Default Re: Need help with a sql-statement


    Captain Paralytic wrote:
     
    > >
    > > I think this is a crosstab problem. To complete it properly (to deal
    > > with n numbers), I think you'd need a stored procedure - something I'm
    > > not at all familiar with. Roland Bouman describes the solution he
    > > provides as not 'hard at all, just a bit tedious':
    > > http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html
    > >
    > > In the comments at the bottom of his page are some links to more
    > > generic solutions - including my favourite, pasting the data into excel
    > > - although if it was me, I'd probably try to find a php solution to the
    > > problem of representing the results.[/ref]
    >
    > That's pretty neat. Does all the OP asked for except for the varying
    > number of columns in the records, which I'm sure is nto possible.[/ref]

    Hmm, not quite sure what you mean CP

    strawberry Guest

  8. #8

    Default Re: Need help with a sql-statement


    strawberry wrote:
     
    > >
    > > That's pretty neat. Does all the OP asked for except for the varying
    > > number of columns in the records, which I'm sure is nto possible.[/ref]
    >
    > Hmm, not quite sure what you mean CP[/ref]

    Well, the stored procedure to produce the crosstab will cope with an
    unknown number of columns.
    So far so good.

    But the results table that the OP posted first was this:

    ers_id|name |tel1 |tel2 |tel3 | tel(n)
    ..............................................
    1 | Meier | 7654654 | 456546 | 5465465 |...
    2 | Müller | 54656 | 5646 | null |...
    3 | Huber | null | null | null
    4 | Schmitt | null | null | null

    I read that as rows 1 and 2 continue for as many columns as are needed
    (the ...)
    But rows 3 and 4 seem to stop at tel3

    Captain Guest

  9. #9

    Default Re: Need help with a sql-statement


    Captain Paralytic wrote:
     
    > >
    > > Hmm, not quite sure what you mean CP[/ref]
    >
    > Well, the stored procedure to produce the crosstab will cope with an
    > unknown number of columns.
    > So far so good.
    >
    > But the results table that the OP posted first was this:
    >
    > ers_id|name |tel1 |tel2 |tel3 | tel(n)
    > .............................................
    > 1 | Meier | 7654654 | 456546 | 5465465 |...
    > 2 | Müller | 54656 | 5646 | null |...
    > 3 | Huber | null | null | null
    > 4 | Schmitt | null | null | null
    >
    > I read that as rows 1 and 2 continue for as many columns as are needed
    > (the ...)
    > But rows 3 and 4 seem to stop at tel3[/ref]

    Ahh, - I read that as a typo (well, actually i missed it altogether). I
    just assumed the OP meant

    'Schmitt | null | null | null...'

    In fact he MUST have meant 'null...', otherwise why would Muller end in
    'null...' ?

    strawberry Guest

  10. #10

    Default Re: Need help with a sql-statement

    Many thanks for you solution

    I see there is no easy way to solve this problem.
    I think the only possibility would be to make an stored procedure
    similar to those with the crosstables.
    But I am not familiar with SP and so I will need too much time.

    For the moment I will try to use the group_concat function and then
    create an table with a variable number of
    columns in php.

    markus43534 Guest

  11. #11

    Default Re: Need help with a sql-statement

    At the end I need an html table which has as much as columns as the
    maximum count of tel-numbers of all persons.

    For example:

    Person 1 has 3 Tel-Numbers.
    Person 2 only one number

    then the html table should look like this:

    <table>
    <tr>
    <td>Müller</td>
    <td>43545</td>
    <td>435543</td>
    <td>5453</td></tr>
    <tr>
    <td>Schmitt</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    </tr>
    </table>

    markus43534 Guest

  12. #12

    Default Re: Need help with a sql-statement

    Sorry there is an mistake in the table.
    This is the correct example:

    <table>
    <tr>
    <td>Müller</td>
    <td>43545</td>
    <td>435543</td>
    <td>5453</td></tr>
    <tr>
    <td>Schmitt</td>
    <td>2224;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    </tr>
    </table>

    markus43534 Guest

  13. #13

    Default Re: Need help with a sql-statement

    At the end I need an html table which has as much as columns as the
    maximum count of tel-numbers of all persons.

    For example:

    Person 1 has 3 Tel-Numbers.
    Person 2 only one number

    then the html table should look like this:

    <table>
    <tr>
    <td>Müller</td>
    <td>43545</td>
    <td>435543</td>
    <td>5453</td></tr>
    <tr>
    <td>Schmitt</td>
    <td>13423</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    </tr>
    </table>
    Markus Guest

  14. #14

    Default Re: Need help with a sql-statement



    On Jan 23, 7:57 pm, Markus <de> wrote: 

    yes, or just create a new 1-row-table for each row and specify the
    width of the cells

    strawberry Guest

Similar Threads

  1. SQL like statement
    By DDhillon in forum Macromedia ColdFusion
    Replies: 18
    Last Post: December 30th, 12:46 PM
  2. sql statement
    By Dan J. Rychlik in forum Macromedia Dynamic HTML
    Replies: 4
    Last Post: October 28th, 03:00 PM
  3. SQL Where Statement
    By dlp1s in forum Dreamweaver AppDev
    Replies: 3
    Last Post: July 13th, 08:03 AM
  4. SQL Statement Help
    By cwwilly in forum ASP Database
    Replies: 9
    Last Post: April 15th, 04:55 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