By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,825 Members | 1,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,825 IT Pros & Developers. It's quick & easy.

SQL-problem: selecting info from 2 tables

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Mattias Campe wrote:
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


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

Jul 17 '05 #2

P: n/a
Ma**********************@UGent.be says...
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?


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
Jul 17 '05 #3

P: n/a
Jay Moore wrote:
Mattias Campe wrote:
<snip problem />
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). :|


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
Jul 17 '05 #4

P: n/a
gmuldoon wrote:
Ma**********************@UGent.be says...

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?

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.


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
Jul 17 '05 #5

P: n/a
Ma**********************@UGent.be says...
the logical one involving *outer joins* in your SQL. Syntax will depend
on which RBDMS you are querying.


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


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
Jul 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.