11 Replies Latest reply on Nov 8, 2012 12:53 AM by BKBK

    How do you join tables on a COUNT query

    TheUrbanBrain

      I have a basic count select statment that pulls back the top occurances of a certain pid in a table.  I am trying to join that table with another table to pull back the details about that pid but I can't seem to get it to work.  Would it be better to use union.

       

      This is my existing query

       

      SELECTpid, COUNT(pid) AS pcount

      FROM Table1

      GROUP BY pid

      ORDER BY pcount DESC

       

      The basic structure doesn't work..

       

      SELECT Table2.name, Table1.pid, COUNT(pid) AS pcount

      FROM Table1, Table2

      WHERE Table1.pid = Table2.pid

      GROUP BY propertyid

      ORDER BY pcount DESC

       

      Does anyone have advice on how to strutcure this... a certain join or a union.  It could be a LOT of records so I don't want to start this with the wrong structure.

       

      Thank you!

       

      This is ColdFusion 10 on MS SQL