SQL Server Join on Select statement using count() and group by

I have two tables in SQL Server, tbl_disputes and tbl_disputetypes. The tbl_disputes table contains a foreign key column disputetype. The table tbl_disputetypes contains the primary key field disputetypeid and disputetypedesc. The following query gives me a count of each disputetype from the tbl_disputes table.

select disputetype, count(disputetype) as numberof
from tbl_disputes
group by disputetype

What sort of join or subquery do I need to use to display the tbl_disputetypes.dbo.disputetypedesc instead of tbl_disputes.dbo.disputetype?

EDIT Issue was because disputetypedesc was s开发者_运维知识库et as TEXT. I changed it to nvarchar, and the following query worked:

SELECT
    tbl_disputetypes.disputetypedesc,
    count(tbl_disputetypes.disputetypedesc)
FROM
    tbl_disputes Left OUTER JOIN
    tbl_disputetypes ON tbl_disputes.disputetype = tbl_disputetypes.disputetypeid
group by tbl_disputetypes.disputetypedesc

---

Unless I'm missing something, you can just LEFT JOIN the description:

select disputetypedesc, count(disputetype) as numberof
from tbl_disputes d
LEFT JOIN tbl_disputetypes dt
    ON dt.disputetypeid = d.disputetype
group by disputetypedesc

---

Assuming 2005+:

WITH x(t, numberof) AS 
(
  SELECT disputetype, COUNT(*) 
    FROM tbl_disputes 
    GROUP BY disputetype
)
SELECT dt.disputetypedesc, x.numberof
  FROM tbl_disputetypes AS dt
  INNER JOIN x ON dt.disputetype = x.t;

---

A simple JOIN?

select
    DT.disputetypedesc, count(*) as numberof
from 
    tbl_disputes D
    JOIN
    tbl_disputetypes DT ON D.disputetype = DT.disputetype
group by
    DT.disputetypedesc

---

The basic idea is that you will need a sub-query. Something like this will work:

select disputetypedesc, disputetype, numberof
from (select disputetype, count(disputetype) numberof
      from tbl_disputes
      group by disputetype) t left outer join
     tbl_disputetypes on t.disputetype = tbl_disputetypes.disputetype

---

I am not sure if I understand your question however you should be able to select all columns using a query similar to the code sample below.

The following query will join the two tables by the disputetypeid column. I changed the format of the SQL statement however you can obviously format it however you would like.

    SELECT tbl_disputetypes.disputetypedesc
         , tbl_disputes.*
         , <any_column_from_either_table>
      FROM tbl_disputes
INNER JOIN tbl_disputetypes
        ON tbl_disputes.disputetypeid = tbl_disputetypes.disputetypeid