Twee keer zelfde table, met JOIN met meerdere voorwaarden

[MrDummy]

Mijn vraag

PHP:

    $sql=mysqli_query($link,
        "SELECT m.id,m.name,m.family,m.level,
            mm.id,mm.name,mm.family,mm.level 
        FROM member m 
        LEFT JOIN member mm
            ON m.family = mm.family 
        WHERE m.clanid='1' AND m.failscan='0' AND mm.failscan='0' 
        ORDER BY m.name ASC,mm.name ASC");

Ik heb één tabel Members.
Ik haal zoals je kan zien alle members eruit met dezelfde clanid.
Maar de members hebben ook family groep. Hier moeten members toegevoegd worden die met dezelfde family groep hebben, maar mogen wel uit andere clanid's komen.
De gekozen query werkt goed, er is wel één nadeel:
In de members lijst zijn ook paar keer members opgenomen (in mijn clan dus) met dezelfde clanid.
Hierdoor pakt het daardoor opnieuw uit LEFT JOIN deel en heb ik dus dubbele rijen die ik al heb kunnen pakken. Dat is dus niet helemaal de bedoeling.
Waardoor de resultaat aantal dubbele namen heeft en veel langer is.
Alleen het is nog niet gelukt om dubbele namen eruit te halen. Ook niet met "m.id<>mm.id" ertussen want dan vallen er aantal rijen weg die wel erbij moeten.
De voorwaarden zijn simpel:
- pak allen met aangegeven clanid=1, dit werkt
- pak dan members erbij met dezelfde family uit dezelfde tabel (met behulp van kopie om ermee te vergelijken), dat doet het ook
- zorg ervoor dat er geen dubbele resultaten voorkomen omdat ze al eerder gepakt zijn (id controle), dit is nog niet goed uitgekomen
- failscan=0 uit beide kanten, werkt hier goed

Relevante software en hardware die ik gebruik.
PHP/MYSQL

Wat ik al gevonden of geprobeerd heb
Heb met INNER JOIN en LEFT JOIN gedaan, geen verbetering.
m.id<>mm.id erbij gedaan in LEFT JOIN - valt rijen weg
in WHERE gaat ook niet goed.

Extra: ik moet ook dezelfde query hebben, maar dan voor mensen buiten clanid=1 inclusief gekoppelde family groep members. Beiden zullen wel bijna identiek zijn, met als verschil clanid<>'1' in WHERE.

Waar heb ik gemist en welke JOIN methode is geschikt?

[GlowMouse]

Dubbele rijen is misleidend, want DISTINCT lost dit niet op.

Als de familierelatie symmetrisch is, kun je (m.id<mm.id OR mm.clanid!='1') gebruiken. Waarom is het id eigenlijk een string?

[ Voor 11% gewijzigd door GlowMouse op 15-04-2018 16:17 ]

[MrDummy]

GlowMouse schreef op zondag 15 april 2018 @ 16:10:
Waarom is het id eigenlijk een string?

Eigenlijk is het al INT ingesteld. Alle ID's zijn als INT. Geen VARCHAR.
Family is VARCHAR string.
failscan is BOOLEAN.

[ Voor 4% gewijzigd door MrDummy op 15-04-2018 16:13 ]

[GlowMouse]

MrDummy schreef op zondag 15 april 2018 @ 16:13:
[...]

Eigenlijk is het al INT ingesteld. Alle ID's zijn als INT. Geen VARCHAR.
Family is VARCHAR string.
failscan is BOOLEAN.

Waarom vergelijk je de integer clanid dan met de string '1'?

[nescafe]

Met de voorwaarde mm.failscan='0' in je WHERE wordt de LEFT JOIN praktisch een INNER JOIN.

Wil je dit per se alleen met JOINs doen of volstaan subselects ook, bijv. zoiets als:

SQL:

SELECT m.id, m.name, m.family, m.level
FROM member m
WHERE (m.clanid = 1 OR m.family IN (SELECT family FROM member WHERE clanid = 1))
  AND m.failscan = 0
ORDER BY m.name ASC

[ Voor 3% gewijzigd door nescafe op 15-04-2018 16:17 ]

[MrDummy]

nescafe schreef op zondag 15 april 2018 @ 16:17:
Met de voorwaarde mm.failscan='0' in je WHERE wordt de LEFT JOIN praktisch een INNER JOIN.

Wil je dit per se alleen met JOINs doen of volstaan subselects ook, bijv. zoiets als:

SQL:

SELECT m.id, m.name, m.family, m.level
FROM member m
WHERE (m.clanid = 1 OR m.family IN (SELECT family FROM member WHERE clanid = 1))
  AND m.failscan = 0
ORDER BY m.name ASC

De members die dezelfde family hebben mogen onafhankelijk zijn. Ze hoeven niet dezelfde clanid hebben.
Neem aan dat WHERE clanid = 1 in 2e SELECT deel weg kan.
Failscan moet gelden voor beide kanten. Dus ook gevonden family rows met failscan=0.
En alle members (zonder family mee te tellen) moeten in clanid=1 zitten.

[ Voor 10% gewijzigd door MrDummy op 15-04-2018 16:24 ]

[nescafe]

MrDummy schreef op zondag 15 april 2018 @ 16:21:
[...]

De members die dezelfde family hebben mogen onafhankelijk zijn. Ze hoeven niet dezelfde clanid hebben.
Neem aan dat WHERE clanid = 1 in 2e SELECT deel weg kan.

Nope, gezien de voorwaarde "Maar de members hebben ook family groep. Hier moeten members toegevoegd worden die met dezelfde family groep hebben, maar mogen wel uit andere clanid's komen." moet er wel degelijk binnen de subselect gefilterd worden op clanid.

Failscan moet gelden voor beide kanten. Dus ook gevonden family rows met failscan=0.

Klopt. Wat er nu niet benoemd is of er ook een member (failscan 0) uit een andere clan mag worden geselecteerd via een member (failscan 1) uit clan 1. Zo niet, dan ook de voorwaarde failscan=0 in de subselect.

En alle members (zonder family mee te tellen) moeten in clanid=1 zitten.

Ik snap deze toevoeging niet echt maar volgens mij is er wel rekening mee gehouden.

Overigens, met JOIN kan het ook, mits je de tweede set resultaatkolommen er uithaalt:

SQL:

SELECT DISTINCT m.id, m.name, m.family, m.level
FROM member m
  LEFT JOIN member m2 ON m.family = m2.family
    AND m2.failscan = 0 -- afh. van hierboven benoemde voorwaarde
WHERE (m.clanid = 1 OR m2.clanid = 1)
  AND m.failscan = 0
ORDER BY m.name ASC

[ Voor 4% gewijzigd door nescafe op 15-04-2018 16:39 ]

[MrDummy]

nescafe schreef op zondag 15 april 2018 @ 16:35:
Nope, gezien de voorwaarde "Maar de members hebben ook family groep. Hier moeten members toegevoegd worden die met dezelfde family groep hebben, maar mogen wel uit andere clanid's komen." moet er wel degelijk binnen de subselect gefilterd worden op clanid.

Deze voorwaarde maakt wat moeilijker om dubbele resultaten te voorkomen want in mijn clan zijn characters (zeg maar members) toegevoegd. Een paar linken elkaar met dezelfde family. Echter ik heb natuurlijk meer characters buiten clan staan. Deze zitten in dezelfde family, kunnen gespeeld worden, maar kunnen niet in de clan ivm ruimte gebrek.

Daarnaast weet ik dat sommige spelers hun characters zelfs in andere clans zetten (vandaar andere clanid) maar omdat 1 van hun characters in mijn clan zitten, kunnen spelers dus desnoods character gewoon pakken ondanks andere clan en dus meehelpen in de raids of parties.
Daarom tellen ze gewoon mee in de zoekopdracht en moeten getoond worden in de overzicht.

Ik weet wel een oplossing maar dat is geen directe query maar opbouwen met while en if loops:
- pak alle members met clanid=1 en in array zetten
- met while loop zoeken naar andere members met gekoppelde family
- met in_array() code nachecken of ze niet al eerder voorkomen
- indien niet voorkomt, dan toevoegen aan array
En dan heb ik juiste array... dat is simpelste dat ik kan doen. Werkt zonder dubbele resultaten.

Directer met query zit net wat anders... ik moet geen dubbele uitkomst hebben door paar characters en alts in dezelfde clan. Het werkt anders wel als iedereen uniek is in de clan.
Daar ben ik dus nog aan het uitzoeken.

[MrDummy]

nescafe schreef op zondag 15 april 2018 @ 16:17:
Met de voorwaarde mm.failscan='0' in je WHERE wordt de LEFT JOIN praktisch een INNER JOIN.

Wil je dit per se alleen met JOINs doen of volstaan subselects ook, bijv. zoiets als:

SQL:

SELECT m.id, m.name, m.family, m.level
FROM member m
WHERE (m.clanid = 1 OR m.family IN (SELECT family FROM member WHERE clanid = 1))
  AND m.failscan = 0
ORDER BY m.name ASC

I must say, this worked very well. No multiple rows but just perfect.

For outside clan i use slight different:

PHP:

$query="SELECT m.id, m.name, m.family, m.level
    FROM member m
    WHERE m.clanid != 1 OR
    (m.clanid != 1 AND m.family IN 
    (SELECT family FROM member WHERE clanid != 1))
    AND m.failscan = 0
    ORDER BY m.id ASC";

Should okay for me now. Perhaps is extra m.clanid != 1 OR not really needed, but i got right results.
I have marked best answer.

The other answers with two tables inside are more harder to keep result clean, i got too much double rows back from it. Other ways with two tables are also not clean. JOIN is not always succesful.
DISTINCT cannot always cover the problem, still some double rows...

Big thanks for working query.

Late noot: ik zie nu pas dat ik in engels tik. Was vergeten dat ik op Nederlandse forum zit en tegelijk Engelse MMO speel. Sorry.
Maar goed, de groen gemarkeerde antwoord heeft goed gewerkt en dus nogmaals mijn dank.

[ Voor 7% gewijzigd door MrDummy op 16-04-2018 10:04 ]