hi!
problem... i have tables:
---
mysql> show fields from Vote;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| ID | int(5) | YES | | NULL | |
| lugu | text | YES | | NULL | |
| band | text | YES | | NULL | |
| last_week | int(7) | YES | | NULL | |
| ip | text | YES | | NULL | |
| kommente | int(7) | YES | | NULL | |
| keskmine | double(4,2) | YES | | NULL | |
| haali | int(7) | YES | | NULL | |
| kuupaev | datetime | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
mysql> show fields from VoteDetail;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| kuupaev | datetime | YES | | NULL | |
| ip | text | YES | | NULL | |
| ID | int(5) | YES | | NULL | |
| kypsis | text | YES | | NULL | |
| haal | int(2) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
---
now i want to select "lugu", "band", "ID" from "Vote" and "count(ID)"
from "VoteDetail" WHERE kypsis = 'something' AND Vote.ID =
VoteDetail.ID...
i tried:
---
mysql> SELECT Vote.lugu, Vote.band, Vote.ID, COUNT(VoteDetail.ID) FROM
Vote, VoteDetail WHERE Vote.ID = Vote.ID;
ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no
GROUP columns is illegal if there is no GROUP BY clause
mysql> SELECT Vote.lugu, Vote.band, Vote.ID, COUNT(VoteDetail.ID) FROM
Vote, VoteDetail WHERE Vote.ID = VoteDetail.ID GROUP BY Vote.ID;
[.. results snipped, all records were selected with correct count ..]
mysql> SELECT Vote.lugu, Vote.band, Vote.ID, COUNT(VoteDetail.ID) FROM
Vote, VoteDetail WHERE Vote.ID = VoteDetail.ID AND VoteDetail.kypsis =
'something' GROUP BY Vote.ID;
[.. results snipped, all records were kypsis WERE 'something' were
selected with correct count ..]
---
problem is i need ALL records! if i have a record in Vote and dont have
corresponding record in VoteDetail WITH kypsis having value 'something'
then i want that column just be 0...
at the moment i have built my script with loop (SELECT ID, band, lugu
FROM Vote; WHILE rows: SELECT COUNT(ID) FROM VoteDetail WHERE kypsis =
'something'; ENDWHILE) but it takes quite long with 300+ rows in first
database + it gives my server quite a load :(
PLZ help...
TIA
regs,
cal6
