select oi.id,oi.name, count(o.id) from orderid oi LEFT OUTER JOIN orders o ON o.order_id = oi.id group by oi.id,oi.name ;
> -----Original Message----- > From: Charles Verge [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, December 03, 2002 11:53 AM > To: [EMAIL PROTECTED] > Subject: join count and 0 > > > > I am wanting to see if there is a way to do a join but > have count(id) show up as 0 when there is no records in > the 2nd table. With out having to 2 selects. > > for example. > > orderid = has persons name and contact info > > > select oi.id,oi.name, count(o.id) from orderid oi, orders o > where o.order_id = oi.id group by oi.id; > > you have say 100 records in orderid , and only 50 of them > have related orders in orders > > for those that don't have matching orders I would > want count(o.id) to show 0 rather then not being listed. > > Any way to do this in a single sql statment ? > > Charles > > > --------------------------------------------------------------------- > 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
