> 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. >
Maybe this might give you an idea, or totally mess you up, but its my = ramblings I have to offer I have no idea if it will even work, but its an idea SELECT orderid.id, orderid.name, orders.id, IF(orders.id IS NULL, '0', = COUNT(orders.id)) AS 'Count' FROM orderid, orders WHERE orders.order_id =3D orderid.id GROUP BY orderid.id, orders.id SQL --------------------------------------------------------------------- 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
