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 wrote:

> 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

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

markus43534 wrote:

> 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

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...-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 wrote:

> markus43534 wrote:
>
> > 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
>
> 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...-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.

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 wrote:

> strawberry wrote:
>
> > markus43534 wrote:
> >
> > > 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
> >
> > 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...-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.
>
> 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

But that only gets up to 3 telephone numbers

strawberry wrote:

> markus43534 wrote:
>
> > 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
>
> 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...-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.

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 Paralytic wrote:

> strawberry wrote:
>
> > markus43534 wrote:
> >
> > > 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
> >
> > 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...-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.
>
> 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.

Hmm, not quite sure what you mean CP

strawberry wrote:

> Captain Paralytic wrote:
>
> > strawberry wrote:
> >
> > > markus43534 wrote:
> > >
> > > > 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
> > >
> > > 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...-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.
> >
> > 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.
>
> Hmm, not quite sure what you mean CP

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 Paralytic wrote:

> strawberry wrote:
>
> > Captain Paralytic wrote:
> >
> > > strawberry wrote:
> > >
> > > > markus43534 wrote:
> > > >
> > > > > 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
> > > >
> > > > 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...-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 tothe
> > > > problem of representing the results.
> > >
> > > 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.
> >
> > Hmm, not quite sure what you mean CP
>
> 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

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

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

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

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.