special join

[Jan Novak]

Hi,

i have a table "server" with auto_increment field ID and a table "ip",
with a filed "SERVER_ID" corrosponding to "server.ID"

select ip.VALUE as "ip" from server, ip where server.DELETED=0 and
server.ID=ip.SERVER_ID

If in "ip" table for the server are more then one ip adress are saved, i
get in my results the server also more then one time listed.

My question is: how can i get only the server with the first IP from the
ip Table, if more then one ip's saved there.

Jan

[Kees Nuyt]

On Mon, 7 Sep 2020 12:26:14 +0200, Jan Novak <rep...@gmail.com>
wrote:

What do you define as "first IP"?
A table is a set, all rows are equal, there is no pre-defined
order.

For more detailed answers, please post the schema of both tables
and example data.
--
Regards,
Kees Nuyt

[Nikolaj Lazic]

Dana Mon, 7 Sep 2020 12:26:14 +0200, Jan Novak <rep...@gmail.com> napis'o:

group by... count... and then select those >1.

https://www.w3resource.com/sql/aggregate-functions/count-with-group-by.php

[Nikolaj Lazic]

Dana Mon, 7 Sep 2020 11:52:21 -0000 (UTC), Nikolaj Lazic <nlazicB...@mudrac.ffzg.hr> napis'o:

https://www.w3resource.com/sql/aggregate-functions/count-having.php

[The Natural Philosopher]

order by....unique

--
When plunder becomes a way of life for a group of men in a society, over
the course of time they create for themselves a legal system that
authorizes it and a moral code that glorifies it.

Frédéric Bastiat

[The Natural Philosopher]

distinct.. Not unique as I said earlier

[Jan Novak]

Am 07.09.20 um 14:15 schrieb The Natural Philosopher:

> On 07/09/2020 11:26, Jan Novak wrote:
>> Hi,
>>
>> i have a table "server" with auto_increment field ID and a table "ip",
>> with a filed "SERVER_ID" corrosponding to "server.ID"
>>
>> select ip.VALUE as "ip" from server, ip where server.DELETED=0 and
>> server.ID=ip.SERVER_ID
>>
>> If in "ip" table for the server are more then one ip adress are saved,
>> i get in my results the server also more then one time listed.
>>
>> My question is: how can i get only the server with the first IP from
>> the ip Table, if more then one ip's saved there.
>>
>> Jan
>
> order by....unique

sorry, i dont understand.
What should be orderd abd unique set?


Jan

[Jan Novak]

Am 07.09.20 um 14:16 schrieb The Natural Philosopher:

> On 07/09/2020 12:52, Nikolaj Lazic wrote:
>> Dana Mon, 7 Sep 2020 12:26:14 +0200, Jan Novak <rep...@gmail.com>
>> napis'o:
>>> Hi,
>>>
>>> i have a table "server" with auto_increment field ID and a table "ip",
>>> with a filed "SERVER_ID" corrosponding to "server.ID"
>>>
>>> select ip.VALUE as "ip" from server, ip where server.DELETED=0 and
>>> server.ID=ip.SERVER_ID
>>>
>>> If in "ip" table for the server are more then one ip adress are saved, i
>>> get in my results the server also more then one time listed.
>>>
>>> My question is: how can i get only the server with the first IP from the
>>> ip Table, if more then one ip's saved there.
>>
>> group by... count... and then select those >1.
>>
>> https://www.w3resource.com/sql/aggregate-functions/count-with-group-by.php
>>
>>
> distinct.. Not unique as I said earlier

i think, thats not the right way.
With distinct, i get different IP Adresses. But in the ip Table, there
are only different ip addresses, therefor it should not help.

Jan

[Jan Novak]

Am 07.09.20 um 14:19 schrieb Jan Novak:

(Sorry for my bad english)

The complete sql string is like that (with additional Infos from "port"
table):

select port.VALUE as "port", ip.VALUE as "ip" from server, port, ip
where server.DELETED=0 and server.ID=port.SERVER_ID and
server.ID=ip.SERVER_ID

In ip Table are for a host 4 rows, but i like to have for the host only
the first one.


Jan

[Tony Mountifield]

In article <rj5air$hr4$1...@gwaiyur.mb-net.net>,

As Kees said in his reply to you, you need to define what you mean by "first".

Here is one possibility, rewritten to use explicit joins instead of implicit joins:

SELECT server.ID, MIN(port.VALUE) AS "port", MIN(ip.VALUE) AS "ip"
FROM server
INNER JOIN ip ON ip.SERVER_ID = server.ID
INNER JOIN port ON port.SERVER_ID = server.ID
WHERE server.DELETED = 0
GROUP BY server.ID

But MIN() is only one possible way to select a single IP or port out of those available.
You need to decide how you want to choose.

Cheers
Tony
--
Tony Mountifield
Work: to...@softins.co.uk - http://www.softins.co.uk
Play: to...@mountifield.org - http://tony.mountifield.org

[J.O. Aho]

What do you mean as first?

- Lowest ip-number (string or value vise)?
- Highest ip-number (string or value vise)?
- First line inserted for the server (lowest id or oldest timestamp)?

For the two first you use a group by with max/min as Tony showed, if you
are thinking of the numeric value and not the string value of the ip,
then you need to cast the value with INET_ATON() (convert it back with
INET_NTOA()).

If you want the first value used for the server, then you will need to
have a way of distinguish which row is inserted first, either by a row
id or a timestamp, in this case you would have to do something like:

SELECT s.ID, p.VALUE AS port, i.VALUE AS ip

FROM server
s
INNER JOIN (SELECT MIN(p2.PORT_ID) PORT_ID, p2.SERVER_ID FROM port p2
WHERE p2.SERVER_ID = s.SERVER_ID GROUP BY p2.SERVER_ID) AS port2 ON
port2.SERVER_ID = s.SERVER_ID
INNER JOIN (SELECT MIN(i2.IP_ID) IP_ID, p2.SERVER_ID FROM ip i2 WHERE
i2.SERVER_ID = s.SERVER_ID GROUP BY p2.SERVER_ID) AS ip2 ON
ip2.SERVER_ID = s.SERVER_ID
INNER JOIN ip i ON i.SERVER_ID = server.ID
and i.IP_ID = ip2.IP_ID
INNER JOIN port p ON p.SERVER_ID = server.ID
and p.PORT_ID = port2.PORT_ID
WHERE server.DELETED = 0


It does have two sub selects, which can slow things down a lot if you
don't have indexes.

--

//Aho

[The Natural Philosopher]

On 07/09/2020 18:42, J.O. Aho wrote:

>
> It does have two sub selects, which can slow things down a lot if you
> don't have indexes.
>

Indices...

--
"If you don’t read the news paper, you are un-informed. If you read the
news paper, you are mis-informed."

Mark Twain

[Jan Novak]

Am 07.09.20 um 17:33 schrieb Tony Mountifield:

>>>>> My question is: how can i get only the server with the first IP from
>>>>> the ip Table, if more then one ip's saved there.

>> The complete sql string is like that (with additional Infos from "port"
>> table):
>>
>> select port.VALUE as "port", ip.VALUE as "ip" from server, port, ip
>> where server.DELETED=0 and server.ID=port.SERVER_ID and
>> server.ID=ip.SERVER_ID
>>
>> In ip Table are for a host 4 rows, but i like to have for the host only
>> the first one.
>
> As Kees said in his reply to you, you need to define what you mean by "first".
>
> Here is one possibility, rewritten to use explicit joins instead of implicit joins:
>
> SELECT server.ID, MIN(port.VALUE) AS "port", MIN(ip.VALUE) AS "ip"
> FROM server
> INNER JOIN ip ON ip.SERVER_ID = server.ID
> INNER JOIN port ON port.SERVER_ID = server.ID
> WHERE server.DELETED = 0
> GROUP BY server.ID
>
> But MIN() is only one possible way to select a single IP or port out of those available.
> You need to decide how you want to choose.

Without the MIN Statement it works like expected. Many thanks for your help.

Jan

[Jan Novak]

Am 07.09.20 um 19:42 schrieb J.O. Aho:

> What do you mean as first?
>
>  - Lowest ip-number (string or value vise)?
>  - Highest ip-number (string or value vise)?
>  - First line inserted for the server (lowest id or oldest timestamp)?
>

> FROM server
>  s
> INNER JOIN (SELECT MIN(p2.PORT_ID) PORT_ID, p2.SERVER_ID FROM port p2
> WHERE p2.SERVER_ID = s.SERVER_ID GROUP BY p2.SERVER_ID) AS port2 ON
> port2.SERVER_ID = s.SERVER_ID
> INNER JOIN (SELECT MIN(i2.IP_ID) IP_ID, p2.SERVER_ID FROM ip i2 WHERE
> i2.SERVER_ID = s.SERVER_ID GROUP BY p2.SERVER_ID) AS ip2 ON
> ip2.SERVER_ID = s.SERVER_ID
> INNER JOIN ip i ON i.SERVER_ID = server.ID
>  and i.IP_ID = ip2.IP_ID
> INNER JOIN port p ON p.SERVER_ID = server.ID
>  and p.PORT_ID = port2.PORT_ID
> WHERE server.DELETED = 0
>
>
> It does have two sub selects, which can slow things down a lot if you
> don't have indexes.

Your suggestion is working also in different ways (i.e. lowest/highest IP).

Thanks a lot.

Jan