joining a mystery

[nanoman]

mysql> select e.name, count(p.event), e.date, e.events, count(c.id) from events e left join pictures p on p.event=e.events left join comments c on c.picture=p.id group by e.events order by e.date desc;

+---------------------------------+----------------+------------+--------+-------------+

| name | count(p.event) | date | events | count(c.id) |

+---------------------------------+----------------+------------+--------+-------------+

| Ayia Napa/Cypern 2001 (nano) | 192 | 2001-06-25 | 0007 | 115 |

| Midsommar 2001 | 135 | 2001-06-22 | 0006 | 5 |

| Baltops + diverse lumpen bilder | 0 | 2001-05-20 | 0005 | 0 |

| Kos 2000 | 63 | 2000-07-05 | 0004 | 4 |

| Studenten | 35 | 2000-06-09 | 0003 | 0 |

| Student Balen | 9 | 2000-05-21 | 0002 | 3 |

| Livigno 2000 | 86 | 2000-02-21 | 0001 | 2 |

+---------------------------------+----------------+------------+--------+-------------+

7 rows in set (0.02 sec)



mysql>



the problem is that i only have 124 images in event 0007, 133 in event 0006 etc etc...

the more comments the more pictures it seems.. in the events with almost no comments the picture ammount is correct. what should i do?



/nano

 

[netcomander]

assuming your tables are:
e, p and c this should work...

select
e.name,
count(p.event),
e.date,
e.events,
count(c.id)
from //notice
(e // you'll need the brackets
left join // ...means all from e
p
on
p.event=e.events)
left join
c
on
c.picture=p.id
group by
e.events
order by
e.date desc

 

[netcomander]

assuming your tables are:
e, p and c this should work...

select
e.name,
count(p.event),
e.date,
e.events,
count(c.id)
from //notice
(e // you'll need the brackets
left join // ...means all from e
p
on
p.event=e.events)
left join
c
on
c.picture=p.id
group by
e.events
order by
e.date desc;


netcomander

 

[netcomander]

sorry for sending this twice

 

[nanoman]

That gives the same result.. too many images on the events where i have many comments. 192 pictures on event 0007 instead of 124. This is wired..

/daniel

 

[nanoman]

the tables:


TABLE EVENTS:
+--------+--------------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+------------+----------------+
| events | int(4) unsigned zerofill | | PRI | NULL | auto_increment |
| name | varchar(128) | | | | |
| date | varchar(10) | | | 0000-00-00 | |
| path | varchar(40) | | | | |
+--------+--------------------------+------+-----+------------+----------------+



TABLE COMMENTS:
+---------+---------------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------------+------+-----+------------+----------------+
| id | int(10) unsigned zerofill | | PRI | NULL | auto_increment |
| comment | blob | YES | | NULL | |
| picture | int(10) unsigned zerofill | | MUL | 0000000000 | |
| date | varchar(14) | YES | | NULL | |
| person | varchar(40) | | MUL | anonym | |
| ip | varchar(20) | YES | | NULL | |
+---------+---------------------------+------+-----+------------+----------------+




TABLE PICTURES:
+-------------+---------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------------+------+-----+---------+----------------+
| id | int(10) unsigned zerofill | | PRI | NULL | auto_increment |
| event | int(4) unsigned zerofill | | MUL | 0000 | |
| file | varchar(255) | | | | |
| description | blob | | | | |
+-------------+---------------------------+------+-----+---------+----------------+