0

I have two tables: Phrase and PhraseCategory

They are linked with Phrase.CategoryId == PhraseCategory.PhraseCategoryShortId

CREATE TABLE [dbo].[Phrase] ( [PhraseId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL, [English] NVARCHAR (250) NOT NULL, [CategoryId] INT NULL, PRIMARY KEY CLUSTERED ([PhraseId] ASC) ); CREATE TABLE [dbo].[PhraseCategory] ( [PhraseCategoryShortId] INT IDENTITY (1, 1) NOT NULL , [Name] VARCHAR (100) NOT NULL, PRIMARY KEY CLUSTERED ([PhraseCategoryShortId] ASC) );

Can someone help give me some advice on how I can join these so that I get a report looking like this:

PhraseCategory.Name Qty

Here's what I have so far:

SELECT PhraseCategory.name, count(*) AS qty FROM Phrase LEFT OUTER JOIN PhraseCategory ON Phrase.CategoryId = PhraseCategory.PhraseCategoryShortId GROUP BY PhraseCategory.name ORDER BY PhraseCategory.name

The problem for me is that I want it to show the Phrase Category name and a 0 if there are no rows with that category. So far I cannot get this to work.

Improve this question

4 Answers 4

Reset to default
1

I think you have the LEFT JOIN backwards:

SELECT pc.name, count(p.CategoryId) AS qty FROM PhraseCategory pc LEFT JOIN Phrase p ON p.CategoryId = pc.PhraseCategoryShortId GROUP BY pc.name ORDER BY pc.name;

You seem to want everything in PhraseCategory, so it should be the first table in the LEFT JOIN. Also note that the COUNT() changed, so it counts the matches in the second table (this is how it returns 0).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Your join is in the wrong order, and you need to count only the records in Phrase, not both tables:

SELECT PhraseCategory.name, count(Phrase.*) AS qty FROM PhraseCategory LEFT OUTER JOIN Phrase ON Phrase.CategoryId = PhraseCategory.PhraseCategoryShortId GROUP BY PhraseCategory.name ORDER BY PhraseCategory.name

Of course, you could change the left join to a right join and keep the same order of tables:

SELECT PhraseCategory.name, count(Phrase.*) AS qty FROM Phrase RIGHT OUTER JOIN PhraseCategory ON Phrase.CategoryId = PhraseCategory.PhraseCategoryShortId GROUP BY PhraseCategory.name ORDER BY PhraseCategory.name
Improve this answer

Comments

0

try IFNULL 

SELECT PhraseCategory.name, IFNULL(count(*),0) AS qty FROM Phrase LEFT OUTER JOIN PhraseCategory ON Phrase.CategoryId = PhraseCategory.PhraseCategoryShortId GROUP BY PhraseCategory.name ORDER BY PhraseCategory.name
Improve this answer

Comments

0

If you want to see counts for both phrases that do not have a category, and categories that do not have phrases, you can use a full outer join.

Since Phrase.CategoryId can be null, you might want to return a string instead of null, e.g. 'No Category'.

select Category = coalesce(pc.name,'No Category') , qty = count(p.PhraseId) from Phrase p full outer join PhraseCategory pc on p.CategoryId = pc.PhraseCategoryShortId group by pc.name order by pc.name;

returns:

+-------------+-----+ | Category | qty | +-------------+-----+ | No Category | 1 | | ColumnNames | 3 | | Functions | 0 | | KeyWords | 2 | | Users | 1 | +-------------+-----+

test setup: http://rextester.com/FUBSY27286

insert into phrase (English, CategoryId) values ('who',null) /* no category */ ,('select',1) ,('from',1) ,('PhraseId',2) ,('English',2) ,('CategoryId',2) ,('Anne',3); insert into PhraseCategory (Name) values ('KeyWords') ,('ColumnNames') ,('Users') ,('Functions') /*no phrases */;
Improve this answer

Comments