CODEWITHSUNDEEP
sqljoin
DMCApps
DMCApps

Reputation: 2168

SQL statement not returning all fields

I have the following SQL statement:

    SELECT r.Result AS Resuly, 
           COUNT(f.fieldID) AS [Count] 
      FROM dbo.Fields f
RIGHT JOIN dbo.Results r ON f.Results = r.ID
     WHERE f.RegionID = @RegionID
  GROUP BY r.Result

What I Would like to statement to do is return all the different results (weither they have a count in the Field DB or not). Currently the Query only returns the values that have a count.

ie in the reuslts DB I have 

ID 1, 2 and 3
Result x, y, z

only x and z have field that require this result in the field DB so I only get back

Result x, z
count 1, 2

what I want is

Result x,y,z
Count 1,(null or 0), 2

Upvotes: 1

Views: 147

Answers (2)

Nicholas Carey
Nicholas Carey

Reputation: 74297

That's because your where clause is filtering out results with no fields.
The where clause is applied after the join is made; criteria in the ON clause is applied before the JOIN is made.

The [conceptual] process for executing a SQL select query is:

  • Compute the cartesian product of all tables in the from clause.
  • Apply the join criteria
  • Apply the where criteria, if such exists.
  • Partition the results set into groups, based on the criteria in the group by clause, if it exists.
  • Compute the values of any summary (aggregate) functions specified, collapsing each group to a single row, whose columns consist solely of aggregate functions, constant values or grouping columns.
  • Apply the criteria specified by the having clause, if such exists.
  • Order the results according to the order by clause, if such exists.
  • Compute the values of the aggregate functions specified in the compute/compute by clauses, if such exists.

So...you need to do this to get what you want:

select Result      = result.Result ,
       ResultCount = count(field.fieldID)
from      dbo.Results result
left join dbo.Fields field   on field.Results  = result.ID
                            and field.RegionID = @RegionID
group by result.Results

The above will give you one row for every result, with a count of matching fields in the specified region (what I believe is what you're asking for). The count will be zero for any result with no matching fields in the specified region.

Upvotes: 6

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

How do we filter results based on the @RegionID parameter, if there are no fields? - it's the WHERE clause that's affecting your result set, since it's asserting a condition about the Fields table. I'm guessing:

SELECT r.Result AS Result, COUNT(f.fieldID) AS [Count] FROM dbo.Fields f
RIGHT OUTER JOIN dbo.Results r ON f.Results = r.ID
WHERE f.RegionID = @RegionID OR f.RegionID is null
GROUP BY r.Result

Upvotes: 0

Related Questions