Re: Problem doing a count query using 2 tables

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 10/17/04

  • Next message: chazz: "Problem doing a count query using 2 tables" 
    Date: Sun, 17 Oct 2004 19:24:08 +0530

    If i understand your requirement correctly, following example may help you.

    Please post relevent table structure/sample records/expected result set to
    understand your problem correctly.

    ex:

    create table emp_master(name varchar(25),city varchar(25))
    create table city_master(city varchar(25),state varchar(25), zip
    varchar(25))
    go
    insert into emp_master values('name1','city1')
    insert into emp_master values('name2','city1')
    insert into emp_master values('name3','city2')
    insert into emp_master values('name4','city2')
    insert into emp_master values('name5','city2')
    insert into emp_master values('name7','city4')
    insert into emp_master values('name8','city5')
    insert into emp_master values('name9','city5')
    insert into city_master values('city1', 'state1', 'xxxx')
    insert into city_master values('city2', 'state1', 'xxxx')
    insert into city_master values('city3', 'state1', 'xxxx')
    insert into city_master values('city4', 'state2', 'xxxx')
    insert into city_master values('city5', 'state3', 'xxxx')
    go
    --query to get the number of states for which records in the first
    table(emp_master) were submitted

    select b.state, count(b.state) as 'no_of_counts'
    from emp_master a join city_master b
    on a.city = b.city
    group by b.state 

    --
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com
  • Next message: chazz: "Problem doing a count query using 2 tables"