MySQL Sub-Query

[dbe]

Hoi,

Ik probeer een MySQL sub-query te maken, maar krijg het niet voor elkaar.

DISTINCT contact, name
  FROM table1
 WHERE id=(select max(id) from table1 WHERE LOWER(contact) LIKE '%van%')
 GROUP BY name

Ik probeer 2 kolommen uit een tabel te krijgen, waar ik de criteria op toepas, zoals in bovenstaande code.

Wat ik wil bereiken, is dat een gebruiker een (deel van een) contactnaam ingeeft, en vervolgens de contactnamen te zien krijgt in een lijstje (met de hostname van de PC van de gezochte contactnaam).

Het huidige probleem is dat de database waar deze query op gedraaid wordt, wekelijks een update doet.
Dit systeem voegt echter steeds data toe, maar verwijderd geen gegevens in de tabel.
Resultaat is dat de database:
1. steeds groeit
2. er dubbels in de database terecht komen (bijvoorbeeld contact A heeft een nieuwe pc gekregen, met een nieuwe Hostname, dus hij staat 2x in de database).

Dit database-systeem (een soort van Network Sniffer) is een aangekochte applicatie van mijn werkgever, en we kunnen er niks aan wijzigen.
De leverancier is met een nieuwe versie bezig waar de database ook een TRUNCATE functie zal bevatten om de dubbels te verwijderen, maar dit kan nog een tijdje duren.
Tot die tijd moet ik met het huidige systeem verder.


Kan iemand me helpen?

Thanks

 

[Frats]

Als je een subquery hebt die meerdere rijen terug kan geven, dan moet je gebruik maken van IN ipv het is-teken.

[sql]
SELECT DISTINCT contact, name
FROM table1
WHERE id IN (SELECT max(id) from table1 WHERE LOWER(contact) LIKE '%van%')
GROUP BY name
[/sql]

Werkt dat beter?

 

[plagvreugd]

@Frats: Inderdaad klopt dat je IN moet gebruiken als de subquery meerdere rijen kan teruggeven, maar deze subquery geeft maar 1 rij terug, namelijk het hoogste id.

@TS: En ik snap niet waarom er aan het eind GROUP BY name staat (zowel bij TS als Frats), mede omdat er in de select een kolom staat die geen group-functie bevat en niet in de group by staat (namelijk contact).

Eigenlijk snap ik vooral niet wat het achterliggende datamodel is: hoe is de tabelstructuur en wat (wat voor rij of rijen) wil je er nou precies uit hebben? Kun je een voorbeeld geven?

 

[dbe]

@Frats: Inderdaad klopt dat je IN moet gebruiken als de subquery meerdere rijen kan teruggeven, maar deze subquery geeft maar 1 rij terug, namelijk het hoogste id.

@TS: En ik snap niet waarom er aan het eind GROUP BY name staat (zowel bij TS als Frats), mede omdat er in de select een kolom staat die geen group-functie bevat en niet in de group by staat (namelijk contact).

Eigenlijk snap ik vooral niet wat het achterliggende datamodel is: hoe is de tabelstructuur en wat (wat voor rij of rijen) wil je er nou precies uit hebben? Kun je een voorbeeld geven?

Hoi Plagvreugd,

Ik heb in bijlage een voorbeeld toegevoegd hoe de MySQL tabel er uit ziet.

Wat ik wil bereiken, is dat ik met de SQL query de kolommen contact en name er uit haal.
De kolom contact wordt door de zoekfunctie van mijn applicatie gebruikt, om de User Names te zoeken die voldoen aan de doorgegeven criteria (bijvoorbeeld zoekstring "van"). De kolom name stelt de computernaam voor van deze gebruiker, en deze wordt dan ook gebruikt door mijn applicatie, om te kijken of deze computer online is op het netwerk (d.m.v. een PING-opdracht).

Je kunt in het voorbeeld zien dat de kolom contact duplicaten bevat. Dit kan doordat de database dynamisch gevuld wordt. Er is een network sniffer die op bepaalde tijdstippen een update zal doen van de op het netwerk gevonden apparaten.
Deze tool heeft echter nog niet de mogelijkheid om zaken automatisch te verwijderen in de database, vandaar de dubbels.

Wat wel een feit is, is dat de gevonden User Name / Host Name die de hoogste ID toegewezen heeft, de meest recente entry is in de database (en dus ook de meest correcte).
Ik wil dus een Query maken, die alle resultaten weergeeft van de ingegeven zoekstring, maar deze resulaten moeten gefilterd worden op de id-waarde, zodat enkel de hoogste waarde (per user name) getoond wordt.

De GROUP BY kan er inderdaad eventueel uit.

Denk je dat dit mogelijk is?

 

[plagvreugd]

Hallo,

Dank voor de info.

Ik het het idee dat je nog steeds twee vragen door elkaar stelt.
Wil je alleen de contact en name van de persoon die je met een parameter opgeeft? Of wil je een lijst met contacten en names van alle contact in de database (en dan van elk contact de info met het hoogste id) ?

Die eerste vraag is vrij makkelijk te beantwoorden, die tweede is wat lastiger en zal ik nu niet beantwoorden omdat ik niet weet of je dat wil en omdat ik weinig tijd heb.

Antwoord op de eerste vraag krijg je met min of meer de query die je al had.
[SQL]
SELECT contact, name
FROM table1
WHERE id = (SELECT max(id) FROM table1 WHERE LOWER(contact) LIKE '%van%')[/SQL]

Gr,
plagvreugd

 

[dbe]

Hallo,

Dank voor de info.

Ik het het idee dat je nog steeds twee vragen door elkaar stelt.
Wil je alleen de contact en name van de persoon die je met een parameter opgeeft? Of wil je een lijst met contacten en names van alle contact in de database (en dan van elk contact de info met het hoogste id) ?

Die eerste vraag is vrij makkelijk te beantwoorden, die tweede is wat lastiger en zal ik nu niet beantwoorden omdat ik niet weet of je dat wil en omdat ik weinig tijd heb.

Antwoord op de eerste vraag krijg je met min of meer de query die je al had.
[SQL]
SELECT contact, name
FROM table1
WHERE id = (SELECT max(id) FROM table1 WHERE LOWER(contact) LIKE '%van%')[/SQL]

Gr,
plagvreugd

Hoi Plagvreugd,

Bedankt voor de info alvast.
Het gaat dus inderdaad de tweede vraag.

Ik wil een lijst van alle contact die aan de zoekstring voldoen, maar dan enkel de resultaten met de hoogste ID-waarde.

 

[plagvreugd]

Helaas, ik snap het nog steeds niet. Je zoekstring bevat een deel van de contactnaam, bijvoorbeeld 'metdepet' en dan wil je dus de kolommen contact en name van het record met het hoogste id dat aan die zoekstring voldoet, oftewel het record met id= 9, dus contact = jan.metdepet en name=ABC1243, toch? Dat is slechts 1 record, niet een verzameling records.

 

[dbe]

Helaas, ik snap het nog steeds niet. Je zoekstring bevat een deel van de contactnaam, bijvoorbeeld 'metdepet' en dan wil je dus de kolommen contact en name van het record met het hoogste id dat aan die zoekstring voldoet, oftewel het record met id= 9, dus contact = jan.metdepet en name=ABC1243, toch? Dat is slechts 1 record, niet een verzameling records.

Ik zal verder verduidelijken op bovenstaande situatie.

Wanneer je de tabelstructuur bekijkt (bijlage) zie je dat er verschillende strings toegevoegd zijn die voldoen aan zoekstring "metdepet", meer bepaald:

• jan.metdepet
• piet.metdepet

jan.metdepet komt 2x voor, meer bepaald:

• id = 1
• id = 9

Wat ik dus wil zien in mijn resultaat is:
jan.metdepet (die afkomstig is van ID = 9)
piet.metdepet

Ik wil dus, wanneer er een dubbele contactnaam is, enkel de informatie zien die afkomstig is van de hoogste ID van deze contactnaam.

't Is wat lastig om uit te leggen...

 

[plagvreugd]

Hallo,

Zo moeilijk is het niet uit te leggen, als je maar meteen met een voorbeeld komt en goed aangeeft wat je nou eigenlijk voor resultaat wil

Hier twee oplossingen, die volgens mij hetzelfde resultaat geven.

[SQL]SELECT contact, name
FROM table1
WHERE id in
(SELECT Hoogste_IDs
FROM (
SELECT max(id) as Hoogste_IDs, contact
FROM table1
WHERE LOWER(contact) LIKE '%van%'
GROUP BY contact
)
)

SELECT contact, name
FROM table1 resultaat
INNER JOIN
( SELECT max(id) as Hoogste_IDs, contact
FROM table1
WHERE LOWER(contact) LIKE '%van%'
GROUP BY contact
) MaxIDs
ON resultaat.ID = MaxIDs.Hoogste_IDs[/SQL]

Succes ermee, laat even weten of t werkt en of je ze begrijpt.

Gr,
plagvreugd