Discussion:
special join
(too old to reply)
Jan Novak
2020-09-07 10:26:14 UTC
Permalink
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
2020-09-07 11:22:13 UTC
Permalink
Post by Jan Novak
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
Nikolaj Lazic
2020-09-07 11:52:21 UTC
Permalink
Post by 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.
group by... count... and then select those >1.

https://www.w3resource.com/sql/aggregate-functions/count-with-group-by.php
Nikolaj Lazic
2020-09-07 11:54:33 UTC
Permalink
Post by Nikolaj Lazic
Post by 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.
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
The Natural Philosopher
2020-09-07 12:16:32 UTC
Permalink
Post by Nikolaj Lazic
Post by 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.
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
Jan Novak
2020-09-07 12:50:09 UTC
Permalink
Post by The Natural Philosopher
Post by Nikolaj Lazic
Post by 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.
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
The Natural Philosopher
2020-09-07 12:15:28 UTC
Permalink
Post by 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
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
Jan Novak
2020-09-07 12:19:04 UTC
Permalink
Post by The Natural Philosopher
Post by 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
order by....unique
sorry, i dont understand.
What should be orderd abd unique set?


Jan
Jan Novak
2020-09-07 12:54:51 UTC
Permalink
Post by Jan Novak
Post by The Natural Philosopher
Post by 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
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
Tony Mountifield
2020-09-07 15:33:38 UTC
Permalink
Post by Jan Novak
Post by Jan Novak
Post by The Natural Philosopher
Post by 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
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"
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: ***@softins.co.uk - http://www.softins.co.uk
Play: ***@mountifield.org - http://tony.mountifield.org
Jan Novak
2020-09-08 10:51:34 UTC
Permalink
Post by Tony Mountifield
Post by Jan Novak
Post by Jan Novak
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"
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".
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
J.O. Aho
2020-09-07 17:42:42 UTC
Permalink
Post by 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.
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
2020-09-08 07:12:27 UTC
Permalink
Post by J.O. Aho
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
2020-09-08 10:53:43 UTC
Permalink
Post by 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

Loading...