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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

Dana Mon, 7 Sep 2020 12:26:14 +0200, Jan Novak <repcom@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

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

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.

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Dana Mon, 7 Sep 2020 12:26:14 +0200, Jan Novak <repcom@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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 07/09/2020 12:52, Nikolaj Lazic wrote:

Dana Mon, 7 Sep 2020 12:26:14 +0200, Jan Novak <repcom@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

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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 <repcom@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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Am 07.09.20 um 14:19 schrieb 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?

(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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In article <rj5air$hr4$1@gwaiyur.mb-net.net>,
Jan Novak <repcom@gmail.com> wrote:

Am 07.09.20 um 14:19 schrieb 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?

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

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: tony@softins.co.uk - http://www.softins.co.uk
Play: tony@mountifield.org - http://tony.mountifield.org

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 07/09/2020 12.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.

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)