[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Join Problem
From:       nkolb () htl ! de
Date:       1999-05-13 21:06:33
[Download RAW message or body]

Hi everybody.

I try to so some selects and got a (for me) unsolveable problem:
 the join-syntax.

You may find a description below and at
http://jester.htl.de/problems/mysql-join.txt


Perhaps anyone can help me.

thanx

Norbert



----------- Description ----------------------------------------
The following tables are present:

select * from domain;

+----+--------------------+--------+-------+-------+------+-----+
| id | domain             | adminc | techc | zonec | prim | sec |
+----+--------------------+--------+-------+-------+------+-----+
|  1 | htl.de             |      1 |     1 |     1 |    7 |   3 |
|  2 | aml-design.de      |      2 |     3 |     3 |    2 |   4 |
|  3 | autoglas-bayern.de |      4 |     3 |     3 |    2 |   4 |
|  4 | baudruide.de       |      5 |     3 |     3 |    2 |   4 |
+----+--------------------+--------+-------+-------+------+-----+

select * from ripe;

+----+-------------+
| id | nichdl      |
+----+-------------+
|  1 | GH226-RIPE  |
|  2 | AD2226-RIPE |
|  3 | JL2020-RIPE |
|  4 | FB1431-RIPE |
|  5 | KL743-RIPE  |
+----+-------------+

select * from nameserver

+----+---------------------+
| id | hostname            |
+----+---------------------+
|  1 | -                   |
|  2 | www.htl.de          |
|  3 | pop.ndrh.de         |
|  4 | pop.k-i-t.de        |
|  5 | pop.k-i-t.net       |
|  6 | ns1.kiosk-online.de |
|  7 | ns.infofax.de       |
|  8 | ns1.nsentry.de      |
|  9 | ns2.nsentry.de      |
| 10 | ns.nameserver1.de   |
| 11 | ns.nameserver5.de   |
+----+---------------------+





My wish is, to do 1 select to get the following result:

+----+--------------------+-------------+-------------+-------------+-----------
----+--------------+
| id | domain             | adminc      | techc       | zonec       | prim     
    | sec          |
+----+--------------------+-------------+-------------+-------------+-----------
----+--------------+
|  1 | htl.de             | GH226-RIPE  | GH226-RIPE  | GH226-RIPE  |
ns.infofax.de | pop.ndrh.de  |
|  2 | aml-design.de      | AD2226-RIPE | JL2020-RIPE | JL2020-RIPE |
www.htl.de    | pop.k-i-t.de |
|  3 | autoglas-bayern.de | FB1431-RIPE | JL2020-RIPE | JL2020-RIPE |
www.htl.de    | pop.k-i-t.de |
|  4 | baudruide.de       | KL743-RIPE  | JL2020-RIPE | JL2020-RIPE |
www.htl.de    | pop.k-i-t.de |
+----+--------------------+-------------+-------------+-------------+-----------
----+--------------+


This works, but gives a incomplete result

select domain.id, domain.domain, ripe.nichdl as adminc, nameserver.hostname as
prim from domain, ripe, nameserver where domain.adminc=ripe.id and
domain.prim=nameserver.id;

+----+--------------------+-------------+---------------+
| id | domain             | adminc      | prim          |
+----+--------------------+-------------+---------------+
|  1 | htl.de             | GH226-RIPE  | ns.infofax.de |
|  2 | aml-design.de      | AD2226-RIPE | www.htl.de    |
|  3 | autoglas-bayern.de | FB1431-RIPE | www.htl.de    |
|  4 | baudruide.de       | KL743-RIPE  | www.htl.de    |
+----+--------------------+-------------+---------------+


Can anyone please send a complete, working select-cmd to nkolb@htl.de


+------------------------------------------
| Kolb Norbert
| Systemadministration HTL Multimedi@
| 
| http://jester.htl.de                            \|/ ____ \|/
|                                                 "@'/ ,. \`@"
| send any critics to /dev/null                   /_| \__/ |_\
| Plees doont mind se spelink misstakss              \__U_/
+-------------------------------------------

---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-thread3331@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail mysql-unsubscribe@lists.mysql.com instead.

[prev in list] [next in list] [prev in thread] [next in thread]