Im really stumped on this one, I have an area on my site where users can enter different contest drawings, I have 2 tables, 1 for the prizes themselves and 1 for the entries. I can pull the prizes and the TOTAL entries, but I am having a hard time figuring out how to pull a specific users entries into each drawing. Here is an example of my tables (which can be changed, they are not used any where else on the site). prizes (table name) prize_id (key), prize_status, prize_name, prize_image --------------- ------------ ------------ ----------- 1, 1, iPhone, image.jpg 2, 1, Backpack, image2.jpg 3, 1, Gift Card, image3.jpg 4, 1, Candle, image4.jpg 5, 1, Something, image5.jpg entries (table name) entry_id (key), prize_id, user_id, outcome ---------------- --------- --------- ---------- 1, 1, 1000, 1 2, 1, 1000, 1 3, 1, 1001, 1 4, 2, 1000, 1 5, 1, 1000, 1 This is my current SQL statement: SELECT prizes.prize_id, prizes.prize_name, prizes.prize_image, SUM(outcome) AS total_entries FROM prizes LEFT OUTER JOIN entries ON (prizes.prize_id = entries.prize_id) WHERE prizes.prize_status = 1 GROUP BY prizes.prize_id; Ideally, I would like to have 1 statement with all the info. I am jaime SUM to get the total entries, easy enough - but I can't think of a way to get the number of entries for each row the user has entered. For example if the users ID was 1000: prize_id, prize_name, prize_image, total_entries, user_entries --------- ------------- ------------- -------------- -------------- 1, iPhone, image.jpg, 4, 3 2, Backpack, image2.jpg, 1, 1 3, Gift Card, image3.jpg, 0, 0 4, Candle, image4.jpg, 0, 0 5, Something, image5.jpg, 0, 0 Any help would be greatly appreciated!!
Hi I'm not at all sure about your database design but I'll leave that for another post. I've made a change or two where I just couldn't leave it as is Check out http://sqlfiddle.com/#!9/ad2910/4 and have a play with the queries CREATE TABLE IF NOT EXISTS `contests` ( `id` int(6) NOT NULL, `status` varchar(20) NOT NULL, `prize_name` varchar(200) NOT NULL, `prize_image` varchar(200) not null, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8; INSERT INTO `contests` (`id`, `status`, `prize_name`, `prize_image`) VALUES ('1', 'Finished', 'iPhone', 'image1.jpg'), ('2', 'Active', 'Backpack', 'image2.jpg'), ('3', 'Active', 'Gift Card', 'image3.jpg'), ('4', 'Draft', 'Candle', 'image4.jpg'), ('5', 'Draft', 'Something', 'image5.jpg'); CREATE TABLE IF NOT EXISTS `entries` ( `id` int(6) NOT NULL, `contest_id` int(6) NOT NULL, `user_id` int(6) NOT NULL, `outcome` varchar(200) not null, `entry_image` varchar(200) null, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8; INSERT INTO `entries` (`id`, `contest_id`, `user_id`, `outcome`, `entry_image`) VALUES ('1', 1, 1000, 0, 'entry1.jpg'), ('2', 1, 1000, 1, 'entry2.jpg'), ('3', 1, 1001, 0, 'entry3.jpg'), ('4', 2, 1000, 0, 'entry4.jpg'), ('5', 2, 1001, 1, 'entry5.jpg'), ('6', 3, 1002, 0, 'entry6.jpg'), ('7', 3, 1003, 0, 'entry7.jpg'); Code (sql): and select `entries`.`id`, `contests`.`prize_name`, `entries`.`outcome`, `entries`.`entry_image` from entries inner join contests on entries.contest_id = contests.id where user_id = 1000; select contests.id, contests.prize_name, contests.status, entries.user_id, count(entries.id) as counter, case when sum(entries.outcome) > 0 then 'winner' else '' end as outcome from entries inner join contests on entries.contest_id = contests.id group by contests.id, entries.user_id order by contests.status, contests.prize_name, entries.user_id; Code (sql):