[mysql] join vraagje

[marty]

Ik heb 3 tabellen:
1 - bevat alle weken van het jaar (en het jaar zelf)
2 - bevat o.a. twee kolomen met een begin en einddatum
3 - bevat o.a. een veld waar op geselecteerd moet worden

Nu wil ik een SELECT query doen die 52 rows teruggeeft, dus:
- selecteer ik alle data uit tabel 1 waar het jaar gelijk is aan (bijv) 2003 en een GROUP BY weeknummer
- doe ik een LEFT JOIN met tabel 2 op die weeknummers waarbij de begindatum voor het weeknummer moet zitten en de einddatum er na
- doe ik een INNER JOIN op tabel 2 met tabel 3 die moet voorkomen dat er data gejoined wordt die niet aan de criteria van dat veld in tabel 3 voldoen

code:

SELECT t3.id, t2.isn, t1.weeknummer, SUM(t2.uren) AS uren
FROM t1
    LEFT JOIN t2 ON YEARWEEK(t2.begindatum) <= t1.jaarweek AND t2.begindatum IS NOT NULL
        AND (YEARWEEK(t2.einddatum) >= t1.jaarweek OR t2.einddatum IS NULL)
    INNER JOIN t3 ON t3.id = t2.isn AND t3.col1 = 'A'
WHERE t1.jaar=2003
GROUP BY t1.weeknummer
ORDER BY t1.weeknummer

Dat werkt echter niet. Als ik een inner join op t2 doe, dan krijg ik 0 rows terug van mysql. Maar ik wil er 52 terughebben waarbij de uren gewoon null zijn (ik wil namelijk van de output een grafiek maken). En als ik een LEFT JOIN doe (bij t3), dan doet ie het verkeerd, want dan wordt er geen rekening gehouden met col1=A (logisch dat ie dat niet doet). Maargoed...hoe moet ik dat dan oplossen?

p.s. jaarweek is een extra kolom die bijv de waarde 200348 bevat (2003 week 48), dit omdat het concatten van jaar en weeknummer het geheel zo traag maakte. door die group by is het al zo traag als dikke teer (en ja, ik gebruik overal indexen)

[Verwijderd]

code:

LEFT JOIN t2 ON YEARWEEK(t2.begindatum) <= t1.jaarweek AND t2.begindatum IS NOT NULL
AND (YEARWEEK(t2.einddatum) >= t1.jaarweek OR t2.einddatum IS NULL)

Klopt dat wel?, volgens mij namelijk niet.

Ik denk dat dit er meer op lijkt:

code:

LEFT JOIN t2 ON ( YEARWEEK(t2.begindatum) <= t1.jaarweek OR t2.begindatum IS NOT NULL )
AND ( YEARWEEK(t2.einddatum) >= t1.jaarweek OR t2.einddatum IS NULL )

[marty]

nee, dat klopt wel. Er mag niets gejoined worden waar de de begindatum NULL is. Dus die OR is niet op z'n plaats. Er mag echter wel gejoined worden als de einddatum niet bekend is, want dat is in princiepe oneindig groot zeg maar.
De query opzich gaat goed, alleen het joinen niet

[marty]

ben er uit waarom het niet lukt, maar vind het wel heel vaag. ik heb ff een dump gemaakt van een voorbeeld zodat jullie precies kunnen zien wat er gebeurd. Als iemand mij dan kan uitleggen waarom dat mislukt zou ik erg blij zijn.

De tabellen met wat data

code:

#
# Table structure for table 'tabel1'
#

CREATE TABLE tabel1 (
  id int(10) unsigned NOT NULL auto_increment,
  jaarweek mediumint(6) unsigned default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY t1_id (id),
  KEY jw (jaarweek)
) TYPE=MyISAM;

#
# Dumping data for table 'tabel1'
#

INSERT INTO tabel1 VALUES("1", "200301");
INSERT INTO tabel1 VALUES("2", "200302");
INSERT INTO tabel1 VALUES("3", "200303");
INSERT INTO tabel1 VALUES("4", "200304");
INSERT INTO tabel1 VALUES("5", "200305");
INSERT INTO tabel1 VALUES("6", "200306");
INSERT INTO tabel1 VALUES("7", "200307");
INSERT INTO tabel1 VALUES("8", "200308");
INSERT INTO tabel1 VALUES("9", "200309");
INSERT INTO tabel1 VALUES("10", "200310");
INSERT INTO tabel1 VALUES("11", "200311");
INSERT INTO tabel1 VALUES("12", "200312");
INSERT INTO tabel1 VALUES("13", "200313");
INSERT INTO tabel1 VALUES("14", "200314");
INSERT INTO tabel1 VALUES("15", "200315");

#
# Table structure for table 'tabel2'
#

CREATE TABLE tabel2 (
  id int(10) unsigned NOT NULL auto_increment,
  date_start date default NULL,
  date_end date default NULL,
  uren tinyint(3) unsigned default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY t2_id (id),
  KEY ds (date_start),
  KEY de (date_end)
) TYPE=MyISAM;

#
# Dumping data for table 'tabel2'
#

INSERT INTO tabel2 VALUES("1", "2002-12-09", "2003-01-05", "20");
INSERT INTO tabel2 VALUES("2", "2002-09-12", "2003-03-03", "30");
INSERT INTO tabel2 VALUES("3", "2001-01-01", NULL, "40");
INSERT INTO tabel2 VALUES("4", "2003-02-02", "2003-04-05", "10");

#
# Table structure for table 'tabel3'
#

CREATE TABLE tabel3 (
  id int(10) unsigned NOT NULL auto_increment,
  tabel2_id int(10) unsigned default NULL,
  beperk enum('true','false') default 'false',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY tabel2_id (tabel2_id)
) TYPE=MyISAM;

#
# Dumping data for table 'tabel3'
#

INSERT INTO tabel3 VALUES("1", "1", "false");
INSERT INTO tabel3 VALUES("2", "2", "false");
INSERT INTO tabel3 VALUES("3", "3", "true");
INSERT INTO tabel3 VALUES("4", "4", "false");

en dan een querie waarbij het goed gaat:

code:

SELECT t3.id AS id3, t2.id AS id2, t1.jaarweek, SUM(t2.uren) AS uren
FROM tabel1 AS t1
  LEFT JOIN tabel2 AS t2 ON YEARWEEK(t2.date_start) <= t1.jaarweek AND t2.date_start IS NOT NULL
    AND (YEARWEEK(t2.date_end) >= t1.jaarweek OR t2.date_end IS NULL)
  INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk != 'true'
GROUP BY t1.jaarweek
ORDER BY t1.jaarweek

Dit is practisch dezelfde query als die ik hier boven deed en deze gaat goed.
De volgende combinaties van de inner join regel gaan allemaal goed:
INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk != 'true'
INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk = 'true'
INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk != 'false'
INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk = 'false'
echter als je er nu dit van maakt:
INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk = 'neither'
Dan krijg je ineens geen rows meer terug!
En ikke dat niet begrijpen....... Ik snap dat er geen data van t2 en t3 gejoined wordt op t1, maar hij zou toch nog steeds de 15 rows uit t1 moeten weergeven?

p.s. de fout zat 'm er ook in dat ik per ongeluk een waarde gaf die niet voorkwam, netzoals neither in het voorbeeld hierboven. dat moest niet en nu werkt het dus, maar ben gewoon benieuwd naar de logica hierachter

[ Voor 13% gewijzigd door marty op 14-04-2003 22:11 ]

[marty]

Wat ik hierboven beschrijf blijkt een bug te zijn die in versie 4.0.13 is gefixed