Results 1 to 4 of 4

Thread: How to query another table from other table result

  1. #1
    Join Date
    Feb 2007
    Posts
    4

    How to query another table from other table result

    Dear Sir,

    I have 2 table. 1 table is keeping all category and another table is the detail of problem including category

    My category table as below

    Access
    Hardware
    Network

    My detail problem table as below

    1 John Access.Missing
    2 Peter Access.NoId
    3 John Hardware.Failure
    4 John Hardware.Missing
    5 Peter Network.Cable
    6 John Network.Hang

    I would like to the count of each category & the result as below

    Access 2
    Hardware 2
    Network 2

    Please help me how I can get result as above.

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    What are the column names in detail table?. It looks like you have the description of category in detail table as well.

    select desc, count(*)
    from detail
    group by desc

    assuming desc is the column name for the problem each user reported.

  3. #3
    Join Date
    Sep 2002
    Posts
    5,938
    On which rdbms? Do you have table schema?

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    create table a (id int, name varchar(50), status varchar(200))

    insert into a select 1, 'John', 'Access.Missing'
    insert into a select 2,'Peter', 'Access.NoId'
    insert into a select 3,'John', 'Hardware.Failure'
    insert into a select 4,'John', 'Hardware.Missing'
    insert into a select 5,'Peter', 'Network.Cable'
    insert into a select 6,'John', 'Network.Hang'
    insert into a select 7,'jane', 'Firewall.Hang'
    insert into a select 8,'Hong', 'opticFibre.cable'
    insert into a select 9,'MAK', 'Telephone.Hang'

    create table c (category varchar(100))
    insert into c select 'Access'
    insert into c select 'Hardware'
    insert into c select 'Network'

    select Category,count(left(status,charindex('.',status)-1) ) as StatusCount from
    a join c on left(status,charindex('.',status)-1)=c.category group by category

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •