[sql] problem z zagnieżdżonym SELECT COUNT Opcje

[pgrzelka]

Witam

mam takie zapytanie

[SQL] pobierz, plaintext 
SELECT `p`.*, (SELECT COUNT(*) FROM `social_program_user` `u` WHERE u.social_program_id=p.social_program_id) AS `users` FROM `social_program` AS `p`
[SQL] pobierz, plaintext 

wyskakuje błąd Column not found: 1054 Unknown column 'u.social_program_id' in 'where clause''

natomiast jeśli przekształcę zapytanie do takiej postaci ( czyli usunę `u` )

[SQL] pobierz, plaintext 
SELECT `p`.*, (SELECT COUNT(*) FROM `social_program_user` WHERE social_program_id=p.social_program_id) AS `users` FROM `social_program` AS `p`
[SQL] pobierz, plaintext 

to liczy mi wszystkie rekordy z tabeli 'social_program_user' ponieważ social_program_id=p.social_program_id zawsze zwraca 1
problemem jest to że w obu tabelach są takie same pola pełniące różne funkcje, poniżej dump z tych tabel

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `social_program` (
  `social_program_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `desc` text,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `admins_id` int(11) DEFAULT NULL,
  `date_begin` date NOT NULL DEFAULT '0000-00-00',
  `date_end` date NOT NULL DEFAULT '0000-00-00',
  `payments_limit` int(255) NOT NULL,
  `points_conversion` float NOT NULL,
  PRIMARY KEY (`social_program_id`) USING BTREE,
  KEY `admins_idx` (`admins_id`) USING BTREE
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `social_program_user` (
  `social_program_user_id` int(11) NOT NULL AUTO_INCREMENT,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `social_program_id` int(11) DEFAULT NULL,
  `social_program_group_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `last_modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`social_program_user_id`) USING BTREE,
  KEY `social_group_idx` (`social_program_group_id`) USING BTREE
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;
[SQL] pobierz, plaintext 

[robsohn]

A takie zapytanie nie rozwiąże problemu ?

[SQL] pobierz, plaintext 
SELECT p.*, COUNT(u.id) FROM social_program AS p LEFT JOIN social_program_user AS u USING(social_program_id) GROUP BY p.social_program_id
[SQL] pobierz, plaintext 

Ten post edytował robsohn 29.04.2010, 16:24:26