SQL-problem: selecting info from 2 tables

[Mattias Campe]

Hello,


although it seems like very easy to solve, I didn't found the right
command to solve the problem, which comes down to following. I've got 2
tables 'telephone' and 'persons'.

Table telephone (lots of records):
N Tel
402 12787
403 12787
....
451 12785


Table persons (3 records):
N First Name Last Name
421 Mark Brian
422 Tim Kerner
450 Mike Womie

When I execute 'select * from persons p, telephone t where p.N = t.N',
I'll normally get sth. like:

N First Name Last Name Tel
421 Mark Brian 12806
422 Tim Kerner 12807
450 Mike Womie 12835


But I actually would like to have:
N First Name Last Name Tel
402 12787
403 12785
....
420 12805
421 Mark Brian 12806
422 Tim Kerner 12807
423
....
450 Mike Womie 12835
451 (empty) (empty) 12785


Is there a way to easily solve this problem, without having to do sth.
with php?


Hopefully sb can help me,
greetings,
Mattias

[Jay Moore]

Re: SQL-problem: selecting info from 2 tables

Mattias Campe wrote:
[color=blue]
> Hello,
>
>
> although it seems like very easy to solve, I didn't found the right
> command to solve the problem, which comes down to following. I've got 2
> tables 'telephone' and 'persons'.
>
> Table telephone (lots of records):
> N Tel
> 402 12787
> 403 12787
> ...
> 451 12785
>
>
> Table persons (3 records):
> N First Name Last Name
> 421 Mark Brian
> 422 Tim Kerner
> 450 Mike Womie
>
> When I execute 'select * from persons p, telephone t where p.N = t.N',
> I'll normally get sth. like:
>
> N First Name Last Name Tel
> 421 Mark Brian 12806
> 422 Tim Kerner 12807
> 450 Mike Womie 12835
>
>
> But I actually would like to have:
> N First Name Last Name Tel
> 402 12787
> 403 12785
> ...
> 420 12805
> 421 Mark Brian 12806
> 422 Tim Kerner 12807
> 423
> ...
> 450 Mike Womie 12835
> 451 (empty) (empty) 12785
>
>
> Is there a way to easily solve this problem, without having to do sth.
> with php?
>
>
> Hopefully sb can help me,
> greetings,
> Mattias[/color]

Try

SELECT p.*, t.tel FROM persons p, telephone t LEFT JOIN ON p.N = t.N
ORDERB BY p.N;

Untested, but I think that'll work. (I think). :|

-Jay

[gmuldoon]

Re: SQL-problem: selecting info from 2 tables

Mattias.NoSpamP lzz.Campe@UGent .be says...
[color=blue]
> But I actually would like to have:
> N First Name Last Name Tel
> 402 12787
> 403 12785
> ...
> 420 12805
> 421 Mark Brian 12806
> 422 Tim Kerner 12807
> 423
> ...
> 450 Mike Womie 12835
> 451 (empty) (empty) 12785
>
>
> Is there a way to easily solve this problem, without having to do sth.
> with php?[/color]

I assume from the example that you have telephones without names, but no
names without telephones. If so, there are two ways to achieve this,
the logical one involving *outer joins* in your SQL. Syntax will depend
on which RBDMS you are querying.

Geoff M

[Mattias Campe]

Re: SQL-problem: selecting info from 2 tables

Jay Moore wrote:[color=blue]
> Mattias Campe wrote:[/color]

<snip problem />
[color=blue]
> Try
>
> SELECT p.*, t.tel FROM persons p, telephone t LEFT JOIN ON p.N = t.N
> ORDERB BY p.N;
>
> Untested, but I think that'll work. (I think). :|[/color]

the syntax needed to be a little bit different (apparently), but by
knowing I needed a LEFT JOIN, I managed to get the solution!

it needed to be sth. like (anyway, that's how it worked ;) ):
SELECT *
FROM telephone t LEFT JOIN persons p
USING (N)
ORDER BY t.n

Thanx Jay!
Greetings,
Mattias

[Mattias Campe]

Re: SQL-problem: selecting info from 2 tables

gmuldoon wrote:[color=blue]
> Mattias.NoSpamP lzz.Campe@UGent .be says...
>
>[color=green]
>>But I actually would like to have:
>>N First Name Last Name Tel
>>402 12787
>>403 12785
>>...
>>420 12805
>>421 Mark Brian 12806
>>422 Tim Kerner 12807
>>423
>>...
>>450 Mike Womie 12835
>>451 (empty) (empty) 12785
>>
>>
>>Is there a way to easily solve this problem, without having to do sth.
>>with php?[/color]
>
>
> I assume from the example that you have telephones without names, but no
> names without telephones. If so, there are two ways to achieve this,
> the logical one involving *outer joins* in your SQL. Syntax will depend
> on which RBDMS you are querying.[/color]

If a 'left join' is an example of an 'outer join', then I managed to get
the solution (in MySQL) :) (see reply on Jays post).

Greetings,
Mattias

[gmuldoon]

Re: SQL-problem: selecting info from 2 tables

Mattias.NoSpamP lzz.Campe@UGent .be says...
[color=blue][color=green]
> > the logical one involving *outer joins* in your SQL. Syntax will depend
> > on which RBDMS you are querying.[/color]
>
> If a 'left join' is an example of an 'outer join', then I managed to get
> the solution (in MySQL) :) (see reply on Jays post).[/color]

Yes, it is.

Cheers,

Geoff

PS.

The other possible solution was a highly inefficient second select
inside a PHP loop.

I suggest you get a good book on ANSI-standard SQL (unfortunately your
MySQL version is likely a bit lacking in things like support for nested
selects). The general rule is that anything you can do at the database
level will be much more efficient than substitute coding in PHP, and
you'll find lots of little tricks to use.

G