www.webdeveloper.com
Results 1 to 4 of 4

Thread: Counting across tables?

Hybrid View

  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Arrow Counting across tables?

    I've been beating my head against this for a while now and I need some help.

    I've got two tables. in Table1 i've got ColA and ColB; in Table2 i've got ColA, ColB, and ColC. Table1.ColA and Table2.ColB contain the same type of information. Table1.ColA is unique, Table2.ColB is not. What query would tell me how many times each item in Table1.ColA appears in Table2.ColB?

    I've tried inner joins, subqueries, left joins, but it just isn't clicking.

  2. #2
    Join Date
    Mar 2010
    Posts
    2,803
    This is untested, but maybe try something like this:

    Code:
     
    select t1.colA, count(*) 
    from table1 t1, table2 t2
    where t1.colA = t2.colB
    group by t1.colA
    all my fingers and toes are crossed

  3. #3
    Join Date
    Jul 2010
    Posts
    2
    Quote Originally Posted by tirna View Post
    This is untested, but maybe try something like this:

    Code:
     
    select t1.colA, count(*) 
    from table1 t1, table2 t2
    where t1.colA = t2.colB
    group by t1.colA
    all my fingers and toes are crossed
    I gave that a try, but it only returned results where the count was at least one. I need a 0 value returned for cases where there is a value in t1.colA that does not match any value in t2.colB

  4. #4
    Join Date
    Dec 2005
    Location
    India
    Posts
    490
    try this

    SELECT a.ColA, (SELECT COUNT(1) FROM table2 where ColB = a.ColA) from table1 a;

    i hope table1.ColA and table2.ColB are indexed.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles