Re: Join Problem

[Christian Mack]

nkolb@stripped wrote:
> 
> 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@stripped
> 
> +------------------------------------------
> | Kolb Norbert

Hi Norbert

You have to JOIN the 'ripe' table and the 'nameserver' table multiple times like this:
SELECT
	domain.id
	, domain.domain
	, r1.nichdl AS adminc
	, r2.nichdl AS techc
	, r3.nichdl AS zonec
	, n1.hostname AS prim
	, n2.hostname AS sec
FROM
	domain
	, ripe AS r1
	, ripe AS r2
	, ripe AS r3
	, nameserver AS n1
	, nameserver AS n2
WHERE
	domain.adminc = r1.id
	AND domain.techc = r2.id
	AND domain.zonec = r3.id
	AND domain.prim = n1.id
	AND domain.sec = n2.id

Tschau
Christian