MySQL, ich bekomme die Abfrage nicht zusammen

[roli]

Hi,

ich komme gerade bei einer Abfrage in MySQL nicht weiter. Ich habe:

Code: Alles auswählen

mysql> SELECT DISTINCT t1.string AS IP_Adr, t2.mac_address
    -> FROM ip_addresses t1, devices t2, activity t3
    -> WHERE t3.device_id = t2.id
    -> AND t3.ip_address_id = t1.id
    -> AND t2.ip_static_id IS NOT NULL
    -> ORDER BY t1.string
    -> LIMIT 0 , 50;
+------------+-------------------+
| IP_Adr     | mac_address       |
+------------+-------------------+
| 10.1.0.254 | 00:xx:21:04:yy:1d |
| 10.1.1.11  | 00:xx:81:79:yy:AD |
| 10.1.1.14  | 00:xx:d3:e6:yy:a2 |
| 10.1.1.171 | 00:xx:8f:d7:yy:81 |
| 10.1.1.172 | 00:xx:d3:e6:yy:e0 |
| 10.1.1.19  | 00:xx:48:91:yy:80 |
| 10.1.1.2   | 00:xx:21:00:yy:DE |
| 10.1.1.2   | 00:xx:21:00:yy:be |
| 10.1.1.2   | 00:xx:D3:E6:yy:F6 |
| 10.1.1.210 | 00:xx:3e:6c:yy:b3 |
| 10.1.1.214 | 00:xx:3e:44:yy:a3 |
| 10.1.1.215 | 00:xx:3e:30:yy:12 |
| 10.1.1.216 | 00:xx:3e:7a:yy:7a |
| 10.1.1.254 | 00:xx:21:00:yy:DE |
| 10.1.1.27  | 00:xx:48:d0:yy:ef |
| 10.1.1.4   | 00:xx:d3:e6:yy:e0 |
| 10.1.1.5   | 00:xx:21:00:yy:DE |
| 10.1.1.5   | 00:xx:48:88:yy:82 |
| 10.1.2.170 | 00:xx:21:00:yy:DE |
| 10.1.2.254 | 00:xx:21:00:yy:be |
+------------+-------------------+
20 rows in set (0.00 sec)

Ich moechte:

Code: Alles auswählen

+------------+-------------------+
| string     | mac_address       |
+------------+-------------------+
| 10.1.1.2   | 00:xx:21:00:yy:DE |
| 10.1.1.2   | 00:xx:21:00:yy:be |
| 10.1.1.2   | 00:xx:D3:E6:yy:F6 |
| 10.1.1.5   | 00:xx:21:00:yy:DE |
| 10.1.1.5   | 00:xx:48:88:yy:82 |
+------------+-------------------+

Also nur die Datensaetze bei denen zu einer IP-Adresse mehere MAC's existieren.

[guenni81]

Mach ein COUNT auf die IP Adressen in deinem Select und selektiere mit der HAVING Klausel alle raus die mehr als ein mal vorkommt.

[roli]

Code: Alles auswählen

SELECT DISTINCT t1.string AS IP_Adr, t2.mac_address, COUNT( t1.string ) AS anzahl
FROM ip_addresses t1, devices t2, activity t3
WHERE t3.device_id = t2.id
AND t3.ip_address_id = t1.id
AND t2.ip_static_id IS NOT NULL
ORDER BY t1.string
LIMIT 0 , 50

ergibt

Code: Alles auswählen

IP_Adr 	mac_address 	anzahl
10.1.0.254 	00:XX:21:04:yy:1d 	6981

Having wird hierbei leider wohl nicht das gewünschte ergebnis bringen

[roli]

Hi,

ich habe jetzt eine Loesung!

Code: Alles auswählen

CREATE TEMPORARY TABLE tt1
SELECT DISTINCT t1.string AS IP_Adr, t2.mac_address AS MAC
FROM ip_addresses t1, devices t2, activity t3
WHERE t3.device_id = t2.id
AND t3.ip_address_id = t1.id
AND t2.ip_static_id IS NOT NULL
ORDER BY t1.string;

CREATE TEMPORARY TABLE tt2
SELECT *, COUNT(IP_Adr) AS anz FROM tt1
GROUP BY IP_Adr;

SELECT a.IP_Adr, a.MAC FROM tt1 a, tt2 b
WHERE a.IP_Adr = b.IP_Adr
AND b.anz >1

Das ist bestimmt nicht die beste/schoenste/... Loesung, aber es geht. Mein letzter JOIN ist halt schon was her ...

[Linaxel]

Probier doch mal :

mysql> SELECT DISTINCT t1.string AS IP_Adr, t2.mac_address
-> FROM ip_addresses t1, devices t2, activity t3
-> WHERE t3.device_id = t2.id
-> AND t3.ip_address_id = t1.id
-> AND t2.ip_static_id IS NOT NULL
AND t1.string in
(
SELECT DISTINCT t1.string AS IP_Adr, count(*)
FROM ip_addresses t1, devices t2, activity t3
WHERE t3.device_id = t2.id
AND t3.ip_address_id = t1.id
AND t2.ip_static_id IS NOT NULL
GROUP by t1.string HAVING count(*) > 2
)
-> ORDER BY t1.string
-> LIMIT 0 , 50;

P.S. keine Garantie für Tippfehler, bin ausserdem mehr an die Oracle-Syntax gewöhnt

Gruß

[roli]

Hi,

die Querry bringt: "#1241 - Operand should contain 1 column(s)"
Wobei Phpmyadmin die beiden count's rot hervorhebt.

[Linaxel]

Sorry, mein Fehler :

Blau markierten Text weglassen:
Aussderm noch ein Denkfehler in Zeile 13 (rot markiert)

mysql> SELECT DISTINCT t1.string AS IP_Adr, t2.mac_address
-> FROM ip_addresses t1, devices t2, activity t3
-> WHERE t3.device_id = t2.id
-> AND t3.ip_address_id = t1.id
-> AND t2.ip_static_id IS NOT NULL
AND t1.string in
(
SELECT DISTINCT t1.string AS IP_Adr , count(*)
FROM ip_addresses t1, devices t2, activity t3
WHERE t3.device_id = t2.id
AND t3.ip_address_id = t1.id
AND t2.ip_static_id IS NOT NULL
GROUP by t1.string HAVING count(*) > 1
)
-> ORDER BY t1.string
-> LIMIT 0 , 50;

Hoffe, es klappt jetzt

Gruß

[roli]

Hi,

jetzt laeuft die Querry und zwar Zeige Datensätze 0 - 18 (19 insgesamt, die Abfrage dauerte 279.1389 sek.), ausserdem sind da eben nicht nur die Datensätze die mehr als eine MAC Adresse zur IP haben, sondern auch die wo es nur eine MAC-IP Kombination gibt.
Neben der "Ergebnis" Unschaerfe kommt noch hinzu das meine Querry Zeige Datensätze 0 - 4 (5 insgesamt, die Abfrage dauerte 0.0004 sek.) geringfuegig schneller ist ;-}