Post reply

Getting number of occurences from one table to another

  • June 11, 2007 at 7:18 am

    #93531

    Hi, I've got a problem.

    I have a table with the follwing rows:

    1

    2

    3

    4

    5

    and another table with the follwing rows

    1

    1

    1

    2

    2

    4

    5

    5

     

    Now I need to get the number of occurences in the 2nd table (0f 1,2 ,3,4,5) in a resultset like this (3rd table)

    1  3

    2  2

    3  0

    4  1

    5  2

    (so three occurences of '1' in table 2, 2 occurences of '2' in table 2 etc.)

    The problem is '3'. How do I get my query to display the number of occurences of '3' when the result must show '0'?

    Pse help!!!

    Phillip

  • June 11, 2007 at 7:47 am

    #711996

    Phillip

    You just need to put in a left join. I setup two tables, tblA and tblB with a column called row_id and placed you values into the tables. With the following query, you get the results you are looking for:

    select

    a.row_id, count(b.row_id)

    from

    tbla a

    left join tblb b

    on a.row_id = b.row_id

    group

    by a.row_id

    Chuck

  • June 11, 2007 at 10:49 pm

    #712165

    Thanks!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply