Probleem met hele grote query
bij de werkende query had ik nog niet toegevoegd wat ik nog nodig had, maar zodra ik dat toevoegde deed hij dus raar...
sorry als jullie me niet begrijpen, maar ik weet niet hoe ik dit probleem goed uit kan leggen
Werkend:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT companies.c_id,
companies.c_name,
companies.rm_id,
relationship_managers.rm_id,
companies.c_invisibility,
relationship_managers.rm_firstname,
relationship_managers.rm_surname
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding ON linkbuilding.c_id = companies.c_id AND linkbuilding.y_id = YEAR(NOW())
WHERE c_invisibility = 0
ORDER BY companies.c_id DESC
companies.c_name,
companies.rm_id,
relationship_managers.rm_id,
companies.c_invisibility,
relationship_managers.rm_firstname,
relationship_managers.rm_surname
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding ON linkbuilding.c_id = companies.c_id AND linkbuilding.y_id = YEAR(NOW())
WHERE c_invisibility = 0
ORDER BY companies.c_id DESC
Niet werkend:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SELECT companies.c_id,
companies.c_name,
companies.rm_id,
relationship_managers.rm_id,
companies.c_invisibility,
relationship_managers.rm_firstname,
relationship_managers.rm_surname,
SUM(l1.y_january) +
SUM(l1.y_february) +
SUM(l1.y_march) +
SUM(l1.y_april) +
SUM(l1.y_may) +
SUM(l1.y_june) +
SUM(l1.y_july) +
SUM(l1.y_august) +
SUM(l1.y_september) +
SUM(l1.y_october) +
SUM(l1.y_november) +
SUM(l1.y_december) +
AS s_totaal
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
JOIN linkbuilding l2 ON l2.c_id = companies.c_id AND l2.y_id = YEAR(NOW())
WHERE c_invisibility = 0
ORDER BY companies.c_id DESC
companies.c_name,
companies.rm_id,
relationship_managers.rm_id,
companies.c_invisibility,
relationship_managers.rm_firstname,
relationship_managers.rm_surname,
SUM(l1.y_january) +
SUM(l1.y_february) +
SUM(l1.y_march) +
SUM(l1.y_april) +
SUM(l1.y_may) +
SUM(l1.y_june) +
SUM(l1.y_july) +
SUM(l1.y_august) +
SUM(l1.y_september) +
SUM(l1.y_october) +
SUM(l1.y_november) +
SUM(l1.y_december) +
AS s_totaal
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
JOIN linkbuilding l2 ON l2.c_id = companies.c_id AND l2.y_id = YEAR(NOW())
WHERE c_invisibility = 0
ORDER BY companies.c_id DESC
Ivo P op 23/04/2014 16:52:26:
ligt eraan wat je groot vindt haha
maar hoezo mis ik een group by? die heb ik volgens mij niet eens nodig...
Dat vereist dat je group by gebruikt.
Ja, je kunt mysql gebruiken zonder dat er een foutmelding verschijnt, maar zoals ik in dat artikeltje aangeef: dan kun je dus willekeurige info terugkrijgen.(en soms klopt die info, maar dat is dan toeval)
Gewijzigd op 23/04/2014 16:58:57 door Ivo P
Dit is enigzins anders dan de fout die de meeste mensen maken met GROUP BY (namelijk wel gebruiken, maar dan niet alle kolommen erin zetten).
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
....
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
JOIN linkbuilding l2 ON l2.c_id = companies.c_id AND l2.y_id = YEAR(NOW())
WHERE c_invisibility = 0
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
JOIN linkbuilding l2 ON l2.c_id = companies.c_id AND l2.y_id = YEAR(NOW())
WHERE c_invisibility = 0
Wat is het nut van die derde join?
Code (php)
1
2
3
4
5
6
2
3
4
5
6
...
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
WHERE c_invisibility = 0 AND l1.y_id = YEAR(NOW())
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
WHERE c_invisibility = 0 AND l1.y_id = YEAR(NOW())
Foutafhandeling inbouwen.
- SanThe - op 23/04/2014 18:53:40:
Regel 20 een + teveel.
Foutafhandeling inbouwen.
Foutafhandeling inbouwen.
haha ja klopt had al weg gehaald :)
Toevoeging op 23/04/2014 22:42:48:
Ger van Steenderen op 23/04/2014 18:24:03:
Wat is het nut van die derde join?
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
....
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
JOIN linkbuilding l2 ON l2.c_id = companies.c_id AND l2.y_id = YEAR(NOW())
WHERE c_invisibility = 0
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
JOIN linkbuilding l2 ON l2.c_id = companies.c_id AND l2.y_id = YEAR(NOW())
WHERE c_invisibility = 0
Wat is het nut van die derde join?
Code (php)
1
2
3
4
5
6
2
3
4
5
6
...
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
WHERE c_invisibility = 0 AND l1.y_id = YEAR(NOW())
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
WHERE c_invisibility = 0 AND l1.y_id = YEAR(NOW())
lastig uit te leggen, maar ik doe het even kort:
ik heb een tabel nodig maar moet 2 verschillende opdrachten krijgen, daarom roep ik hem 2 keer op en geef hem andere naam, anders leveren ze problemen met elkaar op.
Toevoeging op 23/04/2014 22:45:16:
Erwin H op 23/04/2014 17:28:13:
De oplossing van Ivo is helemaal goed, dat vooropgesteld, ik mis alleen de uitleg. Het geval hier is dat je een SUM gebruikt, helemaal zonder GROUP BY. Zelf denk je dat dat niet nodig hebt, maar dan is het altijd zo dat je maar 1 record terugkrijgt. Alles wordt namelijk op een hoop gegooid om een grote SUM te maken. Zonder GROUP BY krijg je dus nooit het 2e record te zien.
Dit is enigzins anders dan de fout die de meeste mensen maken met GROUP BY (namelijk wel gebruiken, maar dan niet alle kolommen erin zetten).
Dit is enigzins anders dan de fout die de meeste mensen maken met GROUP BY (namelijk wel gebruiken, maar dan niet alle kolommen erin zetten).
ty voor de uitleg, dus ik neem aan op het einde van de query GROUP BY s_totaal
Ergo, de join op l2 is nutteloos
Racoon smasher op 23/04/2014 22:40:55:
ty voor de uitleg, dus ik neem aan op het einde van de query GROUP BY s_totaal
Nee, juist niet op s_totaal. Dat is een aggregate kolom. Je moet juist alle andere kolommen in de GROUP BY hebben.
Ger van Steenderen op 23/04/2014 22:49:18:
Die aliassen begrijp ik, maar je doet helemaal niets met l2.
Ergo, de join op l2 is nutteloos
Ergo, de join op l2 is nutteloos
jawel op l2.y_id = YEAR(NOW()), want die year now mag zich niet gaan mengen met l1
Toevoeging op 24/04/2014 09:03:30:
Weet trouwens iemand wat er hier fout aan is?
ik krijg deze error:
Notice: Undefined index: l2.y_april in C:\wamp\www\linkbuilding\beta\check.php on line 62
hij denkt dat de rij in de tabel l2.y_april heet maar l2 is de benaming die ik heb gegeven aan de tabel in de SQL. hoe kan ik dat aan de php vertellen?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
while ($queryFetch = mysql_fetch_assoc($rResult))
{
if (date('m')==1) { $thismonth = $queryFetch['l2.y_januariy']; }
else if (date('m')==2) { $thismonth = $queryFetch['l2.y_february']; }
else if (date('m')==3) { $thismonth = $queryFetch['l2.y_march']; }
else if (date('m')==4) { $thismonth = $queryFetch['l2.y_april']; }
else if (date('m')==5) { $thismonth = $queryFetch['l2.y_may']; }
else if (date('m')==6) { $thismonth = $queryFetch['l2.y_june']; }
else if (date('m')==7) { $thismonth = $queryFetch['l2.y_july']; }
else if (date('m')==8) { $thismonth = $queryFetch['l2.y_august']; }
else if (date('m')==9) { $thismonth = $queryFetch['l2.y_september']; }
else if (date('m')==10) { $thismonth = $queryFetch['l2.y_october']; }
else if (date('m')==11) { $thismonth = $queryFetch['l2.y_november']; }
else if (date('m')==12) { $thismonth = $queryFetch['l2.y_december']; }
echo "<tr style='display: table-row;'>";
echo "<td>" . $queryFetch['c_name'] . "</td>";
echo "<td>" . $queryFetch['rm_firstname'] . " " . $queryFetch['rm_surname'] . "</td>";
echo "<td?" . $thismonth . "</td>";
echo "</tr>";
}
?>
while ($queryFetch = mysql_fetch_assoc($rResult))
{
if (date('m')==1) { $thismonth = $queryFetch['l2.y_januariy']; }
else if (date('m')==2) { $thismonth = $queryFetch['l2.y_february']; }
else if (date('m')==3) { $thismonth = $queryFetch['l2.y_march']; }
else if (date('m')==4) { $thismonth = $queryFetch['l2.y_april']; }
else if (date('m')==5) { $thismonth = $queryFetch['l2.y_may']; }
else if (date('m')==6) { $thismonth = $queryFetch['l2.y_june']; }
else if (date('m')==7) { $thismonth = $queryFetch['l2.y_july']; }
else if (date('m')==8) { $thismonth = $queryFetch['l2.y_august']; }
else if (date('m')==9) { $thismonth = $queryFetch['l2.y_september']; }
else if (date('m')==10) { $thismonth = $queryFetch['l2.y_october']; }
else if (date('m')==11) { $thismonth = $queryFetch['l2.y_november']; }
else if (date('m')==12) { $thismonth = $queryFetch['l2.y_december']; }
echo "<tr style='display: table-row;'>";
echo "<td>" . $queryFetch['c_name'] . "</td>";
echo "<td>" . $queryFetch['rm_firstname'] . " " . $queryFetch['rm_surname'] . "</td>";
echo "<td?" . $thismonth . "</td>";
echo "</tr>";
}
?>
januariy moet january zijn? En zoals al gezegd, kijk eens naar mysqli/pdo. MySQL is verouderd/deprecated en zal op termijn verdwijnen. MySQLi is de improved versie van MySQL en dus vrij makkelijk om over te stappen.
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
<?php
SELECT
l2.y_januariy AS january
FROM
linkbuilding AS l2
while ($queryFetch = mysql_fetch_assoc($rResult)) {
echo $queryFetch['january'];
}
?>
SELECT
l2.y_januariy AS january
FROM
linkbuilding AS l2
while ($queryFetch = mysql_fetch_assoc($rResult)) {
echo $queryFetch['january'];
}
?>
Doe eens een var_dump($queryFetch )
Racoon smasher op 23/04/2014 23:25:21:
jawel op l2.y_id = YEAR(NOW()), want die year now mag zich niet gaan mengen met l1
Ger van Steenderen op 23/04/2014 22:49:18:
Die aliassen begrijp ik, maar je doet helemaal niets met l2.
Ergo, de join op l2 is nutteloos
Ergo, de join op l2 is nutteloos
jawel op l2.y_id = YEAR(NOW()), want die year now mag zich niet gaan mengen met l1
Maar dat doet ie wel.
Michael - op 24/04/2014 09:33:53:
Je kunt niet de tabel meegeven, je zou dan het veld in de query een andere naam moeten geven.
januariy moet january zijn? En zoals al gezegd, kijk eens naar mysqli/pdo. MySQL is verouderd/deprecated en zal op termijn verdwijnen. MySQLi is de improved versie van MySQL en dus vrij makkelijk om over te stappen.
januariy moet january zijn? En zoals al gezegd, kijk eens naar mysqli/pdo. MySQL is verouderd/deprecated en zal op termijn verdwijnen. MySQLi is de improved versie van MySQL en dus vrij makkelijk om over te stappen.
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
<?php
SELECT
l2.y_januariy AS january
FROM
linkbuilding AS l2
while ($queryFetch = mysql_fetch_assoc($rResult)) {
echo $queryFetch['january'];
}
?>
SELECT
l2.y_januariy AS january
FROM
linkbuilding AS l2
while ($queryFetch = mysql_fetch_assoc($rResult)) {
echo $queryFetch['january'];
}
?>
PDO wacht ik wel een tijdje mee en mysqli heb ik al een paar keer uitgeprobeerd om te oefenen, maar ga ik later pas echt mee verder
Grootste verschil met mysql: er staat een i extra in de functienamen en je bent verplicht om de in mysql optionele parameter "verbinding" mee te geven.
En om je daar bewuster mee om te laten gaan, staat die verbinding als eerste genoemd voortaan.
dus:
- i erbij
- verbinding noemen.
- parameters "andersom"
Ivo P op 24/04/2014 09:54:19:
aan mysqli valt niet zo veel te oefenen. Vooral als je de procedurele manier gebruikt.
Grootste verschil met mysql: er staat een i extra in de functienamen en je bent verplicht om de in mysql optionele parameter "verbinding" mee te geven.
En om je daar bewuster mee om te laten gaan, staat die verbinding als eerste genoemd voortaan.
dus:
- i erbij
- verbinding noemen.
- parameters "andersom"
Grootste verschil met mysql: er staat een i extra in de functienamen en je bent verplicht om de in mysql optionele parameter "verbinding" mee te geven.
En om je daar bewuster mee om te laten gaan, staat die verbinding als eerste genoemd voortaan.
dus:
- i erbij
- verbinding noemen.
- parameters "andersom"
Bedankt ik zal het sws een keer toepassen
Toevoeging op 24/04/2014 16:44:48:
De query werkt, maar er is 1 bug...
hij geeft nu namelijk wel alles met de tabellen van 2014 weer, maar stel dat een oud bedrijf nooit een tabel 2014 heeft meegekregen, dan wil ik alsnog de bedrijfsnaam en de relatie beheerder zien.
query:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
SELECT companies.c_id,
companies.c_name,
companies.rm_id,
companies.c_invisibility,
relationship_managers.rm_id,
relationship_managers.rm_firstname,
relationship_managers.rm_surname,
l2.y_january AS january,
l2.y_february AS february,
l2.y_march AS march,
l2.y_april AS april,
l2.y_may AS may,
l2.y_june AS june,
l2.y_july AS july,
l2.y_august AS august,
l2.y_september AS september,
l2.y_october AS october,
l2.y_november AS november,
l2.y_december AS november,
SUM(l1.y_january) +
SUM(l1.y_february) +
SUM(l1.y_march) +
SUM(l1.y_april) +
SUM(l1.y_may) +
SUM(l1.y_june) +
SUM(l1.y_july) +
SUM(l1.y_august) +
SUM(l1.y_september) +
SUM(l1.y_october) +
SUM(l1.y_november) +
SUM(l1.y_december)
AS s_total
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
JOIN linkbuilding l2 ON l2.c_id = companies.c_id
WHERE c_invisibility = 0
AND l2.y_id = YEAR(NOW())
GROUP BY companies.c_id
ORDER BY companies.c_id DESC
companies.c_name,
companies.rm_id,
companies.c_invisibility,
relationship_managers.rm_id,
relationship_managers.rm_firstname,
relationship_managers.rm_surname,
l2.y_january AS january,
l2.y_february AS february,
l2.y_march AS march,
l2.y_april AS april,
l2.y_may AS may,
l2.y_june AS june,
l2.y_july AS july,
l2.y_august AS august,
l2.y_september AS september,
l2.y_october AS october,
l2.y_november AS november,
l2.y_december AS november,
SUM(l1.y_january) +
SUM(l1.y_february) +
SUM(l1.y_march) +
SUM(l1.y_april) +
SUM(l1.y_may) +
SUM(l1.y_june) +
SUM(l1.y_july) +
SUM(l1.y_august) +
SUM(l1.y_september) +
SUM(l1.y_october) +
SUM(l1.y_november) +
SUM(l1.y_december)
AS s_total
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
JOIN linkbuilding l2 ON l2.c_id = companies.c_id
WHERE c_invisibility = 0
AND l2.y_id = YEAR(NOW())
GROUP BY companies.c_id
ORDER BY companies.c_id DESC
met een oude manier van SQL gebruik konden we het probleem wel oplossen, maar dit maakt de SQL te lang.
query op oude manier
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
SELECT companies.c_id,
companies.c_name,
companies.rm_id,
companies.c_invisibility,
relationship_managers.rm_id,
relationship_managers.rm_firstname,
relationship_managers.rm_surname,
(SELECT y_january FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS january,
(SELECT y_february FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS february,
(SELECT y_march FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS march,
(SELECT y_april FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS april,
(SELECT y_may FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS may,
(SELECT y_june FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS june,
(SELECT y_july FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS july,
(SELECT y_august FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS august,
(SELECT y_september FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS september,
(SELECT y_october FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) october,
(SELECT y_november FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS november,
(SELECT y_december FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS december,
SUM(l1.y_january) +
SUM(l1.y_february) +
SUM(l1.y_march) +
SUM(l1.y_april) +
SUM(l1.y_may) +
SUM(l1.y_june) +
SUM(l1.y_july) +
SUM(l1.y_august) +
SUM(l1.y_september) +
SUM(l1.y_october) +
SUM(l1.y_november) +
SUM(l1.y_december)
AS s_total
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
WHERE c_invisibility = 0
GROUP BY companies.c_id
ORDER BY companies.c_id DESC
companies.c_name,
companies.rm_id,
companies.c_invisibility,
relationship_managers.rm_id,
relationship_managers.rm_firstname,
relationship_managers.rm_surname,
(SELECT y_january FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS january,
(SELECT y_february FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS february,
(SELECT y_march FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS march,
(SELECT y_april FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS april,
(SELECT y_may FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS may,
(SELECT y_june FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS june,
(SELECT y_july FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS july,
(SELECT y_august FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS august,
(SELECT y_september FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS september,
(SELECT y_october FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) october,
(SELECT y_november FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS november,
(SELECT y_december FROM linkbuilding l2 WHERE y_id = YEAR(NOW()) and l2.c_id = companies.c_id ) AS december,
SUM(l1.y_january) +
SUM(l1.y_february) +
SUM(l1.y_march) +
SUM(l1.y_april) +
SUM(l1.y_may) +
SUM(l1.y_june) +
SUM(l1.y_july) +
SUM(l1.y_august) +
SUM(l1.y_september) +
SUM(l1.y_october) +
SUM(l1.y_november) +
SUM(l1.y_december)
AS s_total
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
WHERE c_invisibility = 0
GROUP BY companies.c_id
ORDER BY companies.c_id DESC
weet iemand hoe je dit probleem kan oplossen?
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
....
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
LEFT JOIN linkbuilding l2 ON l2.c_id = companies.c_id AND l2.y_id = YEAR(NOW())
WHERE c_invisibility = 0
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
LEFT JOIN linkbuilding l2 ON l2.c_id = companies.c_id AND l2.y_id = YEAR(NOW())
WHERE c_invisibility = 0
Vooropgesteld dat er per bedrijf maar 1 record met y_id 2014 in linkbuilding staat
Gewijzigd op 24/04/2014 18:13:15 door Ger van Steenderen
Ger van Steenderen op 24/04/2014 18:12:02:
Als je dat nu gelijk gezegd had toen ik mijn opmerking plaatste dat die tweede JOIN overbodig was:
Vooropgesteld dat er per bedrijf maar 1 record met y_id 2014 in linkbuilding staat
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
....
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
LEFT JOIN linkbuilding l2 ON l2.c_id = companies.c_id AND l2.y_id = YEAR(NOW())
WHERE c_invisibility = 0
FROM companies
JOIN relationship_managers ON relationship_managers.rm_id = companies.rm_id
JOIN linkbuilding l1 ON l1.c_id = companies.c_id
LEFT JOIN linkbuilding l2 ON l2.c_id = companies.c_id AND l2.y_id = YEAR(NOW())
WHERE c_invisibility = 0
Vooropgesteld dat er per bedrijf maar 1 record met y_id 2014 in linkbuilding staat
Bedankt voor de oplossing, we waren inderdaad bezig met een LEFT JOIN, maar hadden toen AND l2.y_id = YEAR(NOW()) er niet achter staan.
We kwamen pas achter het probleem toen we die andere hadden opgelost...