Hi Dvoracek,
I think you forgot to put firstly "GROUP BY one.id"
so your select statement must be as :
SELECT one.descr, COUNT(two.id) xxx
FROM one LEFT JOIN two USING(id)
GROUP BY one.id, two.id
ORDER BY one.id;
Regards,
Tolga GOZE
Oksijen Teknoloji
www.o2.com.tr
Michal Dvoracek
<michal.dvoracek@c To: [EMAIL PROTECTED]
apitol.cz> cc:
Subject: join & group problem
07/05/2002 08:38
Please respond to
Dvoracek Michal
Hello,
i have 2 tables,
one:
+----+-------+
| id | descr |
+----+-------+
| 1 | fir |
| 2 | two |
| 3 | fre |
| 4 | fou |
+----+-------+
two:
+----+------+
| id | data |
+----+------+
| 1 | a |
| 1 | b |
| 1 | c |
| 1 | d |
+----+------+
SELECT one.descr, COUNT(two.id) xxx FROM one LEFT JOIN two USING(id) GROUP
BY two.id ORDER BY one.id;
i got:
+-------+-----+
| descr | xxx |
+-------+-----+
| fir | 4 |
| two | 0 |
+-------+-----+
2nd row is what(mysql Ver 11.15 Distrib 3.23.43, for pc-linux-gnu (i686))
???
I tested it home and i got only one row (tested on mysql 4.0.1 on win).
but i need this result:
descr count
--------------
fir 4
two 0
fre 0
fou 0
it's possible to get this result in one guery ??
i need that goup by will affect only join, no whole query.
Thank you for help.
Regards,
Michal Dvoracek [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
