Upgrade naar MySQL 5.0.12
Ik heb deze 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
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
SELECT
a.id,
a.title,
a.imageUrl AS articleImageUrl,
a.date,
c.imageUrl AS catImageUrl,
COUNT(r.replyID) AS aant_replies
FROM
gamercc_article a,
gamercc_article_cats c_koppel,
gamercc_category c
LEFT JOIN
gamercc_articlereply r
ON
a.id = r.articleID
WHERE
a.articleType = 'news'
AND
a.id = c_koppel.aid
AND
c.id = c_koppel.cid
AND
DATE_ADD(a.date, INTERVAL 3 DAY) >= NOW()
GROUP BY
a.id
ORDER BY
date DESC
a.id,
a.title,
a.imageUrl AS articleImageUrl,
a.date,
c.imageUrl AS catImageUrl,
COUNT(r.replyID) AS aant_replies
FROM
gamercc_article a,
gamercc_article_cats c_koppel,
gamercc_category c
LEFT JOIN
gamercc_articlereply r
ON
a.id = r.articleID
WHERE
a.articleType = 'news'
AND
a.id = c_koppel.aid
AND
c.id = c_koppel.cid
AND
DATE_ADD(a.date, INTERVAL 3 DAY) >= NOW()
GROUP BY
a.id
ORDER BY
date DESC
Daarop geeft hij deze error:
Invalid query: Unknown column 'a.id' in 'on clause'
Wil iemand misschien de query ombouwen? Misschien beetje veeleisend, maar ik kom er niet uit :S
PS: Ik wil wel met JOIN blijven werken, want ik moet nog een hele website omzetten naar de nieuwe versie, en om er nou allemaal WHERE's voor te gaan gebruiken... Dat is met iets te veel werk.
Gewijzigd op 01/01/1970 01:00:00 door - -
Herjan schreef op 20.11.2006 18:07:
Hoeft niet per se toch? Zo werkt het toch ook?
werkt toch niet ;-)
@Jonathan: de query werkt niet, de aliassen wel.
a.id,
a.title,
a.imageUrl AS articleImageUrl,
a.date,
c.imageUrl AS catImageUrl,
COUNT(r.replyID) AS aant_replies
FROM
gamercc_article a,
gamercc_article_cats c_koppel,
gamercc_category c
LEFT JOIN
gamercc_articlereply r
ON
a.id = r.articleID
WHERE
a.articleType = 'news'
AND
a.id = c_koppel.aid
AND
c.id = c_koppel.cid
AND
DATE_ADD(a.date, INTERVAL 3 DAY) >= NOW()
GROUP BY
a.id
ORDER BY
date DESC
Heb je hem btw al eens in PHPmyadmin gegooid?
Gewijzigd op 01/01/1970 01:00:00 door Klaasjan Boven
Edit:
Ja, gewoon zelfde error
Gewijzigd op 01/01/1970 01:00:00 door - -
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
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
SELECT
a.id,
a.title,
a.imageUrl AS articleImageUrl,
a.date,
c.imageUrl AS catImageUrl,
COUNT(r.replyID) AS aant_replies
FROM
gamercc_article a,
gamercc_article_cats c_koppel,
gamercc_category c
LEFT JOIN
gamercc_articlereply r
ON
(a.id = r.articleID)
WHERE
a.articleType = 'news'
AND
a.id = c_koppel.aid
AND
c.id = c_koppel.cid
AND
DATE_ADD(a.date, INTERVAL 3 DAY) >= NOW()
GROUP BY
a.id
ORDER BY
date DESC
a.id,
a.title,
a.imageUrl AS articleImageUrl,
a.date,
c.imageUrl AS catImageUrl,
COUNT(r.replyID) AS aant_replies
FROM
gamercc_article a,
gamercc_article_cats c_koppel,
gamercc_category c
LEFT JOIN
gamercc_articlereply r
ON
(a.id = r.articleID)
WHERE
a.articleType = 'news'
AND
a.id = c_koppel.aid
AND
c.id = c_koppel.cid
AND
DATE_ADD(a.date, INTERVAL 3 DAY) >= NOW()
GROUP BY
a.id
ORDER BY
date DESC
enzo? zie
mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a);
Ik vind het echt raar. Die manual van MySQL mag trouwens ook wel eens anders opgebouwd worden. (en vertaald in het Nederlands ;) )
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
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
SELECT
a.id,
a.title,
a.imageUrl AS articleImageUrl,
a.date,
c.imageUrl AS catImageUrl,
COUNT(r.replyID) AS aant_replies
FROM
gamercc_article a,
gamercc_article_cats c_koppel,
gamercc_category c
JOIN
gamercc_articlereply r
ON
(id = articleID)
WHERE
a.articleType = 'news'
AND
a.id = c_koppel.aid
AND
c.id = c_koppel.cid
AND
DATE_ADD(a.date, INTERVAL 3 DAY) >= NOW()
GROUP BY
a.id
ORDER BY
date DESC
a.id,
a.title,
a.imageUrl AS articleImageUrl,
a.date,
c.imageUrl AS catImageUrl,
COUNT(r.replyID) AS aant_replies
FROM
gamercc_article a,
gamercc_article_cats c_koppel,
gamercc_category c
JOIN
gamercc_articlereply r
ON
(id = articleID)
WHERE
a.articleType = 'news'
AND
a.id = c_koppel.aid
AND
c.id = c_koppel.cid
AND
DATE_ADD(a.date, INTERVAL 3 DAY) >= NOW()
GROUP BY
a.id
ORDER BY
date DESC
(de tabelnamen in de ON-clause weggehaald)
gamercc_articlereply r
ON
(a.id = r.articleID)
moet dat niet zijn?
LEFT JOIN
gamercc_articlereply
ON
(a.id = gamercc_articlereply.articleID)
Ik zie namelijk op de join site geen alliassen in de JOIN clausule
Heel strange, wat ik in de query in mijn vorige post heb gedaan werkt in een andere query weer niet :S Weird...
1 3
1 5
1 6
of
1 1
2 2
4 4
hier :
JOIN
gamercc_articlereply r
ON
(id = articleID)
?
ik gok 1 op meer
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
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
SELECT
`gamercc_article`.`id`,
`gamercc_article`.`title`,
`gamercc_article`.`imageUrl` AS articleImageUrl,
`gamercc_article`.`date`,
`gamercc_category`.`imageUrl` AS catImageUrl,
COUNT(`gamercc_articlereply`.`replyID`) AS aant_replies
FROM
`gamercc_article`,
`gamercc_article_cats`,
`gamercc_category`
LEFT JOIN
`gamercc_articlereply`
ON
`gamercc_article`.`id` = `gamercc_articlereply`.`articleID`
WHERE
`gamercc_article`.`articleType` = 'news'
AND
`gamercc_article`.`id` = `gamercc_article_cats`.`aid`
AND
`gamercc_article`.`id` = `gamercc_article_cats`.`cid`
AND
DATE_ADD(`gamercc_article`.`date`, INTERVAL 3 DAY) >= NOW()
GROUP BY
`gamercc_article`.`id`
ORDER BY
`gamercc_article`.`date` DESC
`gamercc_article`.`id`,
`gamercc_article`.`title`,
`gamercc_article`.`imageUrl` AS articleImageUrl,
`gamercc_article`.`date`,
`gamercc_category`.`imageUrl` AS catImageUrl,
COUNT(`gamercc_articlereply`.`replyID`) AS aant_replies
FROM
`gamercc_article`,
`gamercc_article_cats`,
`gamercc_category`
LEFT JOIN
`gamercc_articlereply`
ON
`gamercc_article`.`id` = `gamercc_articlereply`.`articleID`
WHERE
`gamercc_article`.`articleType` = 'news'
AND
`gamercc_article`.`id` = `gamercc_article_cats`.`aid`
AND
`gamercc_article`.`id` = `gamercc_article_cats`.`cid`
AND
DATE_ADD(`gamercc_article`.`date`, INTERVAL 3 DAY) >= NOW()
GROUP BY
`gamercc_article`.`id`
ORDER BY
`gamercc_article`.`date` DESC
Yeah, I know, back tics...
Gewijzigd op 01/01/1970 01:00:00 door Andries Louw Wolthuizen
Unknown column 'gamercc_article.id' in 'on clause'