[SQL] Op zoek naar de goede join

[Verwijderd]

Heey,

Ik moet op mijn werk een aardig ingewikkelde query maken (ingewikkeld op mijn SQL kennis niveau) en het is me zelfs bijna gelukt maar nog niet helemaal.
Oke de query moet het volgende resultaat laten zien:

Hij moet alle relaties uit het relatie tabel afgaan, en kijken of die relatie een partner heeft. Als een relatie een partner heeft dan moet hij de gegevens van die partner op dezelfde rij zetten. Indien een relatie geen partner heeft hoort hij hem toch te selecteren maar de partner waarden moeten dan "NULL" zijn.

Oke tot zover is het mij gelukt, en het resultaat mag er wezen. Nu kwam mijn baas, dat hij alle relaties wilt en indien aanwezig een partner welke kinderen hebben. Hoe kan je zien of een relatie een kind heeft, dit kun je zien aan het veld oid1 en oid2 (Ouder Id 1/2). Deze velden verwijzen namelijk naar een relatie welke dus de ouder en of verzorger is van die relatie (kind).
Nu dacht ik dus aan een JOIN waarbij alle relaties die geen kind hebben weg moet laten vallen, echt ben ik hier nog niet in geslaagd en vraag ik om jullie hulp.

Nog even een opmerking, waarschijnlijk ga ik klachten van jullie krijgen over het tabel ontwerp. Ik geef jullie groot gelijk maar het huidige design is ontworpen door iemand die hier werkte 4 jaar geleden.. en dacht niet echt vooruit. Nu zijn we 4 jaar verder en is het tabel al gevult met ruim 10.000 records.

Oke we hebben de volgende query welke alle relaties selecteerd inclusief partner: (deze werkt wel goed execution time +- 0.07... seconden.)

SQL:

-- DEZE Query Selecteerd alle record, gooit partners bij elkaar
-- en selecteerd alleen natuurlijke personen
SELECT
        relatie.sorteerveld    AS relatie_achternaam,
        relatie.straat         AS relatie_straat,
        relatie.huisnummer     AS relatie_huisnummer,
        relatie.postcode       AS relatie_postcode,
        relatie.woonplaats     AS relatie_woonplaats,
        partner.sorteerveld    AS partner_achternaam,
        partner.straat         AS partner_straat,
        partner.huisnummer     AS partner_huisnummer,
        partner.postcode       AS partner_postcode,
        partner.woonplaats     AS partner_woonplaats,
      relatie.pid            AS partnerid     
FROM  klant AS relatie  LEFT JOIN klant AS partner ON ((relatie.pid = partner.id) )
WHERE (relatie.id < relatie.pid OR relatie.pid  IS NULL)
AND   (relatie.oid1 IS NULL AND relatie.oid2 IS NULL)
AND   (partner.oid1 IS NULL AND relatie.oid2 IS NULL)
AND   relatie.soort = "0"
AND   partner.soort = "0"
AND   relatie.cpbedrid IS NULL
AND   partner.cpbedrid IS NULL
ORDER BY partner_achternaam DESC

Maargoed wij willen alleen mensen die samen een kindje hebben gebaart dus ik wat expirimenteren:

SQL:

      relatie.pid            AS partnerid     
FROM  klant AS relatie  LEFT JOIN klant AS partner ON ((relatie.pid = partner.id)
AND ((exists(
        SELECT
                oid1
        FROM    klant
        WHERE   oid1
                IN      (relatie.pid,relatie.id) ))
OR  (exists(
        SELECT  oid2
        FROM    klant
        WHERE   oid2
                IN       (relatie.pid,relatie.id) )) ) )
                
WHERE (relatie.id < relatie.pid OR relatie.pid  IS NULL)
AND   (relatie.oid1 IS NULL AND relatie.oid2 IS NULL)
AND   (partner.oid1 IS NULL AND relatie.oid2 IS NULL)

Ik dacht dus dat je een voorwaarde kon hangen aan een JOIN, maar als ik deze query excute loopt mysql helemaal vast. Dus waarschijnlijk is de relatie onderling niet goed ofzo.

Ik heb ook nog geprobeer een having aan te maken met een count op kinderen (in een subquery) maar dat wou ook niet werken.
Ik heb niet zoveel verstand van joins.. ben er over ana het lezen maar het is moeilijk te bevatten.

Maar is er iemand die mij een tip kan geven, of misschien weet wat ik niet goed doe?

[T-MOB]

Kun je misschien een overzichtje van de tabellen posten (alleen de relevante velden iig). Ik krijg uit je queries niet echt een goed beeld hoe de kinderen worden opgeslagen.

[Fles]

Begrijp ik het goed dat de kinderen ook weer in de tabel klant worden gezet?

[bartware]

Zoals ik het begrijp is er 1 tabel (klant) waarin iedereen staat.
Bij een kind worden met de velden oid1 en oid2 de relaties gelegd naar de beide ouders.

[WormLord]

Volgens mij wil je zoiets dan hebben:

SQL:

      relatie.pid            AS partnerid      
FROM  klant AS relatie  LEFT JOIN klant AS partner ON (relatie.pid = partner.id) 
WHERE (relatie.id < relatie.pid OR relatie.pid  IS NULL) 
AND   (relatie.oid1 IS NULL AND relatie.oid2 IS NULL) 
AND   (partner.oid1 IS NULL AND relatie.oid2 IS NULL)
AND   (exists(
        SELECT 
                oid1 
        FROM    klant 
        WHERE   oid1 IN (relatie.pid,relatie.id)
           OR   oid2 IN (relatie.pid,relatie.id)))

Dus de extra check als conditie van de query en niet van de join en de check op oid1 en oid2 in een keer.

[Verwijderd]

Hier was ik idd al bang voor..

Maargoed, je opmerking is terecht, hier de query om de tabel op te bouwen:

SQL:

CREATE TABLE `klant` (
  `id` int(11) NOT NULL auto_increment,
  `voornaam` varchar(20) default NULL,
  `tussenvoegsel` varchar(10) default NULL,
  `sorteerveld` varchar(25) default NULL,
  `straat` varchar(30) default NULL,
  `huisnummer` varchar(10) default NULL,
  `postcode` varchar(7) default NULL,
  `woonplaats` varchar(50) default NULL,
  `staat` varchar(15) default NULL,
  `telthuis` varchar(15) default NULL,
  `telwerk` varchar(15) default NULL,
  `mobiel` varchar(15) default NULL,
  `inkomenpj` float(8,2) default NULL,
  `werkgever` varchar(50) default NULL,
  `codesoort` varchar(30) default NULL,
  `gebdatum` date default NULL,
  `geslacht` char(1) default NULL,
  `huis` varchar(10) default NULL,
  `mid` int(11) default NULL,
  `email` varchar(50) default NULL,
  `coid` int(11) default NULL,
  `brid` int(11) default NULL,
  `mobiel2` varchar(15) default NULL,
  `email2` varchar(50) default NULL,
  `functie` varchar(50) default NULL,
  `contract` varchar(50) default NULL,
  `sofi` varchar(20) default NULL,
  `voorletters` varchar(10) default NULL,
  `aanspreektitel` varchar(20) default NULL,
  `memo` blob,
  `girobank` varchar(20) default NULL,
  `pkid` int(11) default NULL,
  `identificatie` varchar(20) default NULL,
  `ostraat` varchar(30) default NULL,
  `ohuisnummer` varchar(10) default NULL,
  `opostcode` varchar(7) default NULL,
  `owoonplaats` varchar(50) default NULL,
  `otelthuis` varchar(15) default NULL,
  `nid` int(11) default NULL,
  `mailing` varchar(5) default NULL,
  `titel` varchar(20) default NULL,
  `volledigenaam` varchar(47) default NULL,
  `soort` int(11) default '0',
  `smid` int(11) default '0',
  `relatiecode` varchar(10) default NULL,
  `padres` varchar(30) default NULL,
  `ppostcode` varchar(7) default NULL,
  `pplaats` varchar(30) default NULL,
  `fax` varchar(15) default NULL,
  `url` varchar(30) default NULL,
  `pid` int(11) default NULL,
  `oid1` int(11) default NULL,
  `oid2` int(11) default NULL,
  `cpbedrid` int(11) default NULL,
  `wijzig_medew` varchar(30) default NULL,
  `wijzig_tijd` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `aanmaak_medew` varchar(30) NOT NULL default '',
  `aanmaak_tijd` datetime NOT NULL default '0000-00-00 00:00:00',
  `peradres` varchar(30) default NULL,
  `land` varchar(30) default NULL,
  `pland` varchar(30) default NULL,
  `tmpmailingmaart` tinyint(1) NOT NULL default '0',
  `verwijderd` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4918 ;

Behoorlijke puinhoop he?

Maargoed.. Zowel kinderen, relaties en partners worden allemaal in dezelfde tabel opgeslagen. (tabel klant.. ookwel relatie genoemd)
Een partner is gekoppeld middels het "pid" veld (Partner ID) hierin staat het ID nummer van de partner en bij die partner staat weer een verwijzing terug zeg maar.

Voor kinderen geld het zo, deze zijn een aparte record in de tabel klant (relatie), maar in kolom oid1 en oid2 staat een verwijzing naar de ouders van records uit hetzelfde tabel.

Hopelijk is het zo wat duidelijker

[T-MOB]

Kun je hem dan niet 2 keer joinen:

code:

SELECT blaat FROM klant AS relatie
INNER JOIN klant AS partner ON (relatie.pid = partner.id)
LEFT JOIN klant AS child ON (
     (child.oid1 = relatie.id AND child.oid2 = partner.id) OR 
     (child.oid2 = relatie.id AND child.oid1 = partner.id) )

Zou een lijst geven met alle relaties, eventueel met kinderen van deze 2. Bij meerdere kinderen krijg je meerdere rijen.

[Fles]

code:

SELECT 
  relatie.voornaam AS relatie_voornaam, 
  partner.voornaam AS partner_voornaam, 
  kind.voornaam AS kind_voornaam
FROM klant AS kind
INNER JOIN klant AS relatie
  ON (kind.oid1 = relatie.id OR kind.oid2 = relatie.id)
INNER JOIN klant AS partner
  ON relatie.pid = partner.id

Nog een keer edit:
Moet de relatie samen met de HUIDIGE partner kinden hebben of moet hij uberhaupt kinderen hebben? Je weet hoe dat gaat tegenwoordig, scheiden, hertrouwen en kinderen achterlaten

[ Voor 45% gewijzigd door Fles op 27-10-2005 14:52 ]

[Verwijderd]

Haha inderdaad.

Ik heb de querys gezien. De volgende query gaf resultaat:

SQL:

-- DEZE Query Selecteerd alle record, gooit partners bij elkaar
-- en selecteerd alleen natuurlijke personen
SELECT
        relatie.sorteerveld    AS relatie_achternaam,
        relatie.straat         AS relatie_straat,
        relatie.huisnummer     AS relatie_huisnummer,
        relatie.postcode       AS relatie_postcode,
        relatie.woonplaats     AS relatie_woonplaats,
        partner.sorteerveld    AS partner_achternaam,
        partner.straat         AS partner_straat,
        partner.huisnummer     AS partner_huisnummer,
        partner.postcode       AS partner_postcode,
        partner.woonplaats     AS partner_woonplaats,
      relatie.pid            AS partnerid     
FROM  klant AS relatie  LEFT JOIN klant AS partner ON ((relatie.pid = partner.id) )
                
WHERE (relatie.id < relatie.pid OR relatie.pid  IS NULL)
AND   (relatie.oid1 IS NULL AND relatie.oid2 IS NULL)
AND   (partner.oid1 IS NULL AND relatie.oid2 IS NULL)
AND   relatie.soort = "0"
AND   partner.soort = "0"
AND   relatie.cpbedrid IS NULL
AND   partner.cpbedrid IS NULL
AND   (exists(
        SELECT
                oid1
        FROM    klant
        WHERE   oid1 IN (relatie.pid,relatie.id)
           OR   oid2 IN (relatie.pid,relatie.id)))
ORDER BY partner_achternaam DESC

maar de execution time was 180 seconden is wel wat veel

Maargoed.

Hij moet alle relaties laten zien inclusief partner.. welke minstens een kind hebben.

Nu bedenk ik me net dat hij waarschijnlijk vast loopt omdat het een LEFT JOIN is (bij een left join hoeft een relatie niet persee een partner te hebben ?) Dus zou ik een INNER JOIN moeten hebben ?

Overigens, ben ik nu eventjes een paar uurtjes weg dus kan niet meer reageren.
Ik zal het geposte commentaar eens proberen

[T-MOB]

Verwijderd schreef op donderdag 27 oktober 2005 @ 14:43:
maar de execution time was 180 seconden is wel wat veel

Wat indexen op pid, oid1, oid2 zouden sowieso niet misstaan....

[ Voor 76% gewijzigd door T-MOB op 27-10-2005 14:44 ]

[Fles]

*CORRECTIE*

Hehehehe, dacht dat ik een keer een mod hier had horen zeggen dat LEFT JOIN sneller was dan INNER JOIN.

NIET WAAR dus

Het moet hier geen wikipedia worden met onjuistheden

[ Voor 107% gewijzigd door Fles op 27-10-2005 14:52 ]

[curry684]

INNER JOIN is per definitie sneller dan iedere andere join.

[Fles]

Als de relatie en partner SAMEN één of meer kinderen moeten hebben:

code:

SELECT 
  relatie.voornaam AS relatie_voornaam, 
  partner.voornaam AS partner_voornaam, 
  kind.voornaam AS kind_voornaam
FROM klant AS kind
INNER JOIN klant AS relatie
  ON (kind.oid1 = relatie.id OR kind.oid2 = relatie.id)
INNER JOIN klant AS partner
  ON relatie.pid = partner.id
WHERE
  kind.oid1 = partner.id
  OR kind.oid2 = partner.id

[P_de_B]

curry684 schreef op donderdag 27 oktober 2005 @ 14:48:
INNER JOIN is per definitie sneller dan iedere andere join.

Waarom zou een inner join per definitie sneller zijn dan een left outer join

[Verwijderd]

Waarom is INNER-JOIN sneller leg dat eens uit want dat snap ik niet ?