Page 1 of 1

count along with table details

Posted: Wed Dec 12, 2007 3:34 am
by thatsme
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I have 2 tables,

t1, t2




[b]t1[/b]
t1_id    name    selected_flag
1             a             1
2             b             1
3             c             1
4             d             1




[b]t2[/b]
t2_id  t1_id  flag
  1        1       1
  2        1       1
  3        1       0
  4        2       0

I want the count of each element of t1 which are having flag 1 in table t2 along with the element name.  
In the example above, i should be getting 
countname = 2, a
countname = 0, b
countname = 0, c
countname = 0, d
[syntax="sql"]
SELECT t1.*, t2.COUNT(flag) as countname FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t1_id WHERE t1.selected_flag = 1 AND t2.flag=1 GROUP BY t1.t1_id
The above query giving me expected output only when flag is 1.


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]