[MySQL] Hopeloos vast bij een query

[Joep]

Ik ben bezig met een database en loop vast bij een query:

code:

SELECT description FROM authorization WHERE id=(SELECT authorization_id FROM ad_group WHERE ad_group.id='1')

Deze query werkt wel, sterker nog, doet precies wat ik wil, maar ik heb het gevoel alsof dit veel makkelijker en/of beter kan, maar het lukt me niet met een join, omdat ik uit moet gaan van ad_group.id='1'.

Ik wil dus dat de query authorization.description opvraagt waar ad_group.id='1', rekening houdend met de relaties tussen de tabellen. Iemand?

Zie:



Een authorization (met type=account) kan 0,1 of meerdere Active Directory groepen hebben en een Active Directory groep hoort altijd bij een authorization (account).

[sanderdw]

SELECT description
FROM authorization as a
INNER JOIN ad_group as b
on a.id = b.authorization_id
where b.id='1'

[ Voor 6% gewijzigd door sanderdw op 14-06-2014 14:51 ]

[Joep]

Derp Bedankt!

[Joep]

M'n eerste vraag was bij nader inzien erg simpel, maar ik denk dat m'n volgende een uitdaging is.

De database heeft nog een aantal tabellen, waaronder de onderste 3. agent_skill is de koppeltabel tussen agent en skill.



Nu wil ik alle agent.id's selecteren waarvan skill_id niet gelijk is aan 3. Het probleem is echter dat een agent meerdere skills kan hebben en een agent_id dus meerdere malen kan voorkomen met telkens een ander skill_id. Als ik dus zeg WHERE skill_id != 3, dan krijg ik toch agent.id 506902 terug, omdat die agent ook een andere skill heeft met een ander id. Daarnaast komen sommige agents niet eens voor in de tabel agent_skill.

Een overzicht van de data:

code:

SELECT id, agent_id, skill_id FROM agent LEFT JOIN agent_skill ON agent.id = agent_skill.agent_id)

Mijn poging:

code:

SELECT id, agent_id, skill_id FROM agent LEFT JOIN agent_skill ON agent.id = agent_skill.agent_id WHERE skill_id != 3 OR skill_id IS NULL

Wat krijg ik terug:



Niet de bedoeling dus, want agents 506902 en 518167 hebben dus wel skill_id 3.

Ik wil dit graag oplossen in een MySQL query en niet door de geretourneerde array in PHP met een IF statement aan te passen, maar ik vraag me af of dit wel gaat lukken. Ik denk dat ik iets heel stoms over het hoofd zie

[Verwijderd]

In dit geval zou je je kunnen afvragen of het niet makkelijker is om dit met een subquery te doen:

SQL:

SELECT   id
FROM     agent
WHERE    id NOT IN(
    SELECT agent_id
    FROM   agent_skill
    WHERE  skill_id = 3
)
ORDER BY id ASC

Soms is het niet zo moeilijk.

[Merethil]

Joep schreef op maandag 16 juni 2014 @ 21:56:
M'n eerste vraag was bij nader inzien erg simpel, maar ik denk dat m'n volgende een uitdaging is.

De database heeft nog een aantal tabellen, waaronder de onderste 3. agent_skill is de koppeltabel tussen agent en skill.

[afbeelding]

Nu wil ik alle agent.id's selecteren waarvan skill_id niet gelijk is aan 3. Het probleem is echter dat een agent meerdere skills kan hebben en een agent_id dus meerdere malen kan voorkomen met telkens een ander skill_id. Als ik dus zeg WHERE skill_id != 3, dan krijg ik toch agent.id 506902 terug, omdat die agent ook een andere skill heeft met een ander id. Daarnaast komen sommige agents niet eens voor in de tabel agent_skill.

Een overzicht van de data:

code:

SELECT id, agent_id, skill_id FROM agent LEFT JOIN agent_skill ON agent.id = agent_skill.agent_id)

[afbeelding]

Mijn poging:

code:

SELECT id, agent_id, skill_id FROM agent LEFT JOIN agent_skill ON agent.id = agent_skill.agent_id WHERE skill_id != 3 OR skill_id IS NULL

Wat krijg ik terug:

[afbeelding]

Niet de bedoeling dus, want agents 506902 en 518167 hebben dus wel skill_id 3.

Ik wil dit graag oplossen in een MySQL query en niet door de geretourneerde array in PHP met een IF statement aan te passen, maar ik vraag me af of dit wel gaat lukken. Ik denk dat ik iets heel stoms over het hoofd zie

Niet de allermooiste van de queries, maar hij doet wat je wilt tegen een niet al te zware penalty:

SQL:

SELECT id, agent_id, skill_id 
FROM agent 
JOIN agent_skill ON agent.id = agent_skill.agent_id
WHERE agent.id NOT IN (SELECT agent_id FROM agent_skill WHERE skill_id = 3)

Dit kan ook met een set LEFT joins, maar deze leek me duidelijker en ik kon zo even niet bedenken hoe je de joins moest opbouwen.

Edit: Bah, spuit-11... Rot-Cheatah

[Joep]

Thx! Ook vrij logsich bij nader inzien. Kan dit ook zonder subquery?

[Verwijderd]

Wat is het probleem?

[Merethil]

Joep schreef op maandag 16 juni 2014 @ 22:16:
Thx! Ook vrij logsich bij nader inzien. Kan dit ook zonder subquery?

Waarom wil je deze aanpak eigenlijk? Kan je niet beter inclusive werken -> Dus ervoor zorgen dat alles gevonden wordt wat wél een bepaalde agent_id heeft, ipv de andere kant op waarbij iets níet een bepaalde agent_id heeft?

[Joep]

Ik wil niet persé zonder subquery werken, maar afaik zijn subqueries CPU-intensief (hoewel een gekunstelde query die hetzelfde doet, maar geen gebruik maakt van een subquery, waarschijnlijk evenveel of meer resources zal vragen). Nu zal het geen vaart lopen in mijn simpele DB, maar ik ben gewoon benieuwd/leergierig.

[albino71]

Volgens mij is de vraag: Waarom wil je iedereen zien die NIET skill 3 heeft

[Merethil]

Joep schreef op maandag 16 juni 2014 @ 22:24:
Ik wil niet persé zonder subquery werken, maar afaik zijn subqueries CPU-intensief (hoewel een gekunstelde query die hetzelfde doet, maar geen gebruik maakt van een subquery, waarschijnlijk evenveel of meer resources zal vragen). Nu zal het geen vaart lopen in mijn simpele DB, maar ik ben gewoon benieuwd/leergierig.

Over het algemeen doen joins niet veel meer dan een subquery encapsulaten. Eventueel een snellere / betere subquery dan wat je zelf kan maken, maar het zal weinig schelen.
Als je het toch echt wilt zou je waarschijnlijk twee joins op dezelfde tabel moeten gaan doen, weet niet of dat zo netjes wordt.

albino71 schreef op maandag 16 juni 2014 @ 22:25:
Volgens mij is de vraag: Waarom wil je iedereen zien die NIET skill 3 heeft

Dit is idd wat ik bedoelde met inclusive/exclusive proberen te queryen. Het is gewoon een handigere aanpak om inclusive te werken.

[ Voor 18% gewijzigd door Merethil op 16-06-2014 22:27 ]

[Dido]

albino71 schreef op maandag 16 juni 2014 @ 22:25:
Volgens mij is de vraag: Waarom wil je iedereen zien die NIET skill 3 heeft

Daar zijn wel business cases voor te verzinnen natuurlijk.

Ik wil n aeen cursus skill3 toevoegen aan een paar agents, die ik selecteer uit een lijst. Dan wil ik dus precies die lijst van alle agents zonder skill 3.

[Joep]

@ Dido: Exact wat ik wil doen idd. Een dropdownlist bevolken. U read my mind Brb

[Merethil]

Joep schreef op maandag 16 juni 2014 @ 22:28:
@ Dido: Exact wat ik wil doen idd. Een dropdownlist bevolken. U read my mind Brb

Wil je een dropdownlist bevolken met dingen die je wilt excluden?

[Verwijderd]

Merethil schreef op maandag 16 juni 2014 @ 22:29:

Wil je een dropdownlist bevolken met dingen die je wilt excluden?

Zodat je bijvoorbeeld een persoon kunt toewijzen aan een cursus. Dat is niet zo nuttig met mensen die die skill al hebben.

Joep schreef op maandag 16 juni 2014 @ 22:16:
Thx! Ook vrij logsich bij nader inzien. Kan dit ook zonder subquery?

Nou, vooruit dan maar.

SQL:

SELECT    id
FROM      agent
LEFT JOIN agent_skill ON agent.id = agent_skill.agent_id
GROUP BY  id
HAVING    SUM(skill_id = 3) = 0
    OR    SUM(skill_id = 3) IS NULL;

Ik denk niet dat het makkelijker is dan een subquery en hoop dan ook maar dat je hem snapt.

[ Voor 6% gewijzigd door Verwijderd op 16-06-2014 22:42 . Reden: rekening houden met agents zonder skills ;) ]

[Joep]

Merethil schreef op maandag 16 juni 2014 @ 22:29:
[...]


Wil je een dropdownlist bevolken met dingen die je wilt excluden?

Nee, natuurlijk niet Het tegenovergestelde zoals ik al zei:

Joep schreef op maandag 16 juni 2014 @ 21:56:
Nu wil ik alle agent.id's selecteren waarvan skill_id niet gelijk is aan 3.

Edit: Ben ff je post aan 't lezen Cheatah...

Edit 2: Ik denk dat ik 'm begrijp.
HAVING SUM(skill_id = 3) = 0 geeft SUM van boolean 1, dus 1 voor agents met skill_id 3 en SUM van boolean 0, dus 0 voor agents zonder skill_id 3 maar wel met een ander skill_id.
OR SUM(skill_id = 3) IS NULL geeft SUM van NULL, dus NULL als agent niet voorkomt in agent_skill

Correct?

[ Voor 30% gewijzigd door Joep op 16-06-2014 23:10 ]

[Gomez12]

Verwijderd schreef op maandag 16 juni 2014 @ 22:33:
[...]

Nou, vooruit dan maar.

SQL:

SELECT    id
FROM      agent
LEFT JOIN agent_skill ON agent.id = agent_skill.agent_id
GROUP BY  id
HAVING    SUM(skill_id = 3) = 0
    OR    SUM(skill_id = 3) IS NULL;

Ik denk niet dat het makkelijker is dan een subquery en hoop dan ook maar dat je hem snapt.

Alhoewel deze ook werkt zou ik toch altijd gaan voor de subquery. Die is imho namelijk 100x leesbaarder.
En veelal ook iets van 1000x performanter te schrijven dan dit soort "baksels".

Doe bovenstaande query maar eens over 2 miljoen records en met 5 condities, dan hakt het niet kunnen gebruiken van indexen er erg hard in, terwijl je met de subquery gewoon de inner query volledige indexen kan laten gebruiken, dan koppel je inner query aan outer query met indexen en met een beetje rdbms (jammer dat mysql dan afvalt maar dat is pech) heb je nog steeds performance.

[Verwijderd]

Helemaal mee eens. Ik wilde alleen maar laten zien dat het kan maar dat je er weinig mee opschiet. Het is inderdaad minder duidelijk en in elk geval veel minder simpel dan "selecteer uit deze set wat niet in die set zit"

[Sikkek]

Of anders:

SQL:

SELECT DISTINCT id 
FROM      agent 
LEFT JOIN agent_skill 
    ON    agent.id = agent_skill.agent_id 
    AND   skill_id = 3
WHERE     agent_skill.skill_id IS NULL

Geen subqueries en wel leesbaar.

[Joep]

@ Sikkek, DISTINCT is niet eens nodig. Eens kijken of ik je query begrijp.

Edit: Geweldig, jouw query is naar mijn mening de beste. Of iedereen het daar mee eens is weet ik niet, maar ik vind 'm overzichtelijk/duidelijk en ik denk dat 'ie sneller is dan de rest.

Nogmaals, iedereen bedankt voor de hulp

[ Voor 61% gewijzigd door Joep op 17-06-2014 15:30 ]