Sun Maung Oo Sun Maung Oo - 10 days ago 3
SQL Question

COUNT() return 0 on certain column

Requirements:I wanted to know how many images that each person have posted.

Therefore I create a table schema as follow.

Table=Person
==========
Id (PK) , Column1, Column2, LId (FK)

Table=ListMaster
============
Id (PK) , LId (Unique)

Table = ListDetail
===========
Id (PK), LId(FK), DataId(FK)

Table = Image
=========
Id (PK), Column1, Column2


and use a query SQL

SELECT Person.Id AS PersonId,
Person.Column1 AS PersonC1,
Person.Column2 AS PersonC2,
COUNT(Image.Id) AS ImageCount
FROM Person
LEFT OUTER JOIN ListMaster ON Person.LId = ListMaster.LId
LEFT OUTER JOIN ListDetail ON ListDetail.LId = ListMaster.LId
LEFT OUTER JOIN Data AS Image ON ListDetail.DataId = Image.Id
GROUP BY Person.Id,
Person.Column1,
Person.Column2


I notice some person's "ImageCount" have "0" although there are images that he have posted before.

Could you please advise me how to fix my query or tell me it is even logically possible to do what I wanted? I suspect I make a mistake regarding my table design.

Sample Data
=======

Table = Person

Id (PK)(bigint identity) | Column1 (nvarchar(max)) | Column2 (nvarchar(max)) | LId (FK) (bigint [null])

1 | Test 1 C1 | Test1 C2 | 1

2 | Test 2 C1 | Test 2 C2 | 2

3 | Test 3 C1 | Test 3 C2 | NULL

4 | Test 4 C1 | Test 4 C4 | 37

Table = ListMaster

Id (PK)(bigint)(identity) | LId (Unique)(bigint)

1 | 1

2 | 2

3 | 37

Table = ListDetail



Id (PK)(bigint identity)| LId(FK)(bigint not null)| DataId(FK)(bigint not null)
1 | 1 | 1

2 | 1 | 2

3 | 2 | 3

4 | 37 | 4

Table = Image

Id (PK)(big int not null)(identity) | Column1 (nvarchar(max)) | Column2 (nvarchar(max))

1 | Location 1 | Dummy Data 1

2 | Location 2 | Dummy Data 2

3 | Location 3 | Dummy Data 3

4 | Location 4 | Dummy Data 4


I expect the COUNT(Image.Id) AS ImageCount should return

2
1
0
1


but it return

2
1
0
0


EDIT 1 : Change the table design

EDIT 2 : Add a sample data

Jay Jay
Answer

It looks like your query and data as given should return the values you expect. Is this the actual schema, data, and query, or are you simplifying to make this post? I presume your real data does not include values like "test 1 C1", etc. Did you create a DB with these dummy field names and values to do this test, or are you saying that this is the equivalent of what you really have? If this isn't the actual stuff, it could well be that in simplifying you have left out the thing that's really causing the problem.

When I have a query that does not give the expected results, I try to drop out parts of the query to see where the problem is. Like try with only the first join and see if you get the expected results. If that works, add in the second join, etc. Leave out the GROUP BY and just dump all the records, so you can see the actual records and not just the count.

There are many possible sources of trouble. Maybe the data isn't what you think it is. Maybe one of the joins is using the wrong field. Maybe you're getting in trouble because you have different data types and a conversion is not giving the results you expect. Etc.

Comments