Topic met hoogste rating laten zien?
Ik heb 3 tabellen:
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
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
mysql> DESCRIBE schaap_r;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(9) | NO | PRI | NULL | auto_increment |
| tid | int(9) | NO | MUL | | |
| datum | datetime | NO | | | |
| bericht | text | NO | | | |
| naam | varchar(50) | NO | | | |
| ip | varchar(50) | NO | | | |
+---------+-------------+------+-----+---------+----------------+
6 rows in set (0.07 sec)
mysql> DESCRIBE schaap_t;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(9) | NO | PRI | NULL | auto_increment |
| door | varchar(20) | NO | | | |
| datum | datetime | NO | | | |
| titel | varchar(100) | NO | | | |
| bericht | text | NO | | | |
| foto | varchar(255) | NO | | | |
| ip | varchar(50) | NO | | | |
+---------+--------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
mysql> DESCRIBE schaap_rates;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(9) | NO | PRI | NULL | auto_increment |
| tid | int(9) | NO | MUL | | |
| rating | int(1) | NO | | | |
| ip | varchar(50) | NO | | | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(9) | NO | PRI | NULL | auto_increment |
| tid | int(9) | NO | MUL | | |
| datum | datetime | NO | | | |
| bericht | text | NO | | | |
| naam | varchar(50) | NO | | | |
| ip | varchar(50) | NO | | | |
+---------+-------------+------+-----+---------+----------------+
6 rows in set (0.07 sec)
mysql> DESCRIBE schaap_t;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(9) | NO | PRI | NULL | auto_increment |
| door | varchar(20) | NO | | | |
| datum | datetime | NO | | | |
| titel | varchar(100) | NO | | | |
| bericht | text | NO | | | |
| foto | varchar(255) | NO | | | |
| ip | varchar(50) | NO | | | |
+---------+--------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
mysql> DESCRIBE schaap_rates;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(9) | NO | PRI | NULL | auto_increment |
| tid | int(9) | NO | MUL | | |
| rating | int(1) | NO | | | |
| ip | varchar(50) | NO | | | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
Alleen nu wil ik de 10 topics (schaap_t) laten zien, die de hoogste rating hebben. Dit allemaal in 1 query. Ik kom er alleen neit uit :(
Iemand anders misschein wel?
Gr.
Niek
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT
schaap_t.titel
FROM
schaap_t,
schaap_rates
WHERE
schaap_t.id = schaap_rates.tid
ORDER BY
schaap_rates.rating DESC
LIMIT 0, 10
schaap_t.titel
FROM
schaap_t,
schaap_rates
WHERE
schaap_t.id = schaap_rates.tid
ORDER BY
schaap_rates.rating DESC
LIMIT 0, 10
Niet getest, maar zoiets zal het worden.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
SELECT
schaap_t.titel,
AVG(schaap_rates.rating) AS gemiddelde
FROM
schaap_t,
schaap_rates
WHERE
schaap_t.id = schaap_rates.tid
GROUP BY
schaap_t.id
ORDER BY
gemiddelde DESC
schaap_t.titel,
AVG(schaap_rates.rating) AS gemiddelde
FROM
schaap_t,
schaap_rates
WHERE
schaap_t.id = schaap_rates.tid
GROUP BY
schaap_t.id
ORDER BY
gemiddelde DESC
Het kan denk ik ook met een JOIN:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
schaap_t.titel
AVG(schaap_rates.rating) AS gemiddelde
FROM
schaap_t
OUTER_JOIN
schaap_rates
ON
schaap_t.id = schaap_rates.tid
GROUP BY
schaap_t.id
ORDER BY
gemiddelde DESC
schaap_t.titel
AVG(schaap_rates.rating) AS gemiddelde
FROM
schaap_t
OUTER_JOIN
schaap_rates
ON
schaap_t.id = schaap_rates.tid
GROUP BY
schaap_t.id
ORDER BY
gemiddelde DESC
Dit zou (uit mijn hoofd) ook moeten werken. (sorry, na die join tutorials heb ik de smaak een beetje te pakken)
Dus deze twee zijn hetzelfde:
Code (php)
1
2
3
2
3
SELECT t1.kolom
FROM t1, t2
WHERE t1.id = t2.t1_id
FROM t1, t2
WHERE t1.id = t2.t1_id
Code (php)
1
2
3
4
2
3
4
SELECT t1.kolom
FROM t1
INNER JOIN t2
ON t1.id = t2.t1_id
FROM t1
INNER JOIN t2
ON t1.id = t2.t1_id