CODEWITHSUNDEEP
sqlsql-serversql-server-2008
spraus
spraus

Reputation: 9

MSSQL select single row with all references counted

I am attempting to do something using MSSQL that I believe is possible (easily) but I do not know how to vocalize the correct search string. I have the situation below.

Table A
UID  | Value....
1    | a
2    | b
3    | c


Table B
PartTypes_uid_fk   | Value....
1           | a
1           | b
1           | c
1           | d
1           | e
3           | 67
3           | 1354

I am attempting to get the following result, query Table A for all results {TableA.*} and on the same row result show the number of table b references {count TableB.tableA_fk} What I have so far is the following.

SELECT DISTINCT t1.uid, CONVERT(varchar(MAX), t1.Name) AS Name, CONVERT(varchar(MAX), t1.Description) AS Description, Count(t2.Items_uid_fk) OVER (Partition By t2.PartTypes_uid_fk) as Count FROM [Table1] as t1 left outer join Table2 as t2 on t2.PartTypes_uid_fk=t1.uid;

This works for all of Table A records with an associated record in Table B but if there are 0 entries in Table B it won't work. The conversion of the varchars was required due to the fact they are ntext format and it was distinct.

Thank you for all your help in advance. Stephen

Upvotes: 1

Views: 538

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107786

Instead of running into problems with the GROUP BY on N/TEXT columns, and to run faster, you would want to pre-aggregate the B table and LEFT JOIN that against A.

select t1.*, ISNULL(t2.c, 0) AS CountOfB
from table1 t1
left join
(
    select parttypes_uid_fk, count(*) c
    from table2
    group by parttypes_uid_fk
) t2 on t2.PartTypes_uid_fk=t1.uid

Upvotes: 1

rs.
rs.

Reputation: 27467

Your query should be

SELECT t1.uid, 
CONVERT(varchar(MAX), t1.Name) AS Name, 
CONVERT(varchar(MAX), t1.Description) AS Description, 
Count(t2.Items_uid_fk) CountItems 
FROM [Table1] as t1 left outer join Table2 as t2 on t1.uid = t2.PartTypes_uid_fk
GROUP BY uid, t1.name, t1.Description;

Upvotes: 0

Eli Gassert
Eli Gassert

Reputation: 9763

It's easier than that:

SELECT t1.uid, t1.Name, COUNT(*)
FROM [Table1] t1
LEFT JOIN [Table2] t2 ON t2.PartTypes_uid_fk = t1.uid
GROUP BY t1.uid, t1.Name

Upvotes: 0

Related Questions