CODEWITHSUNDEEP
sqlsql-server
Sandile Chonco
Sandile Chonco

Reputation: 1

Sql joins returning Null Results

I have 4 tables that i am trying to get data from yet my join statement returns null. I have tried full, left and right joins with no success. Blow i have provided my code and the diagram, the table that returns null values is the ChannelData table. i am trying to sum the Vvalue from this table:

DECLARE @bp varchar(4)
DECLARE @priority varchar(2)
DECLARE @startDate datetime
DECLARE @endDate datetime

SET @bp = 1710
SET @priority = 2

SET @endDate = (SELECT EndDate FROM BillingPeriod WHERE BillingPeriodClass_ID = 1 AND CODE = @BP)
SET @startDate = (SELECT EndDate FROM BillingPeriod WHERE BillingPeriodClass_ID = 1 AND CODE = @BP -1 )
SET @startDate = dateadd(minute, 1, @startDate)


SELECT  CGS.Description,
        CD.VValue, DI.Margin, @priority AS PRIORITY

FROM DataIntegrity AS DI

FULL JOIN CGS AS CGS ON CGS.ID = DI.CGS_ID_1 OR CGS.ID = DI.CGS_ID_2 
LEFT JOIN ChannelParameter AS CP ON CP.ID = CGS.ID
LEFT JOIN ChannelData AS CD ON CP.ID = CD.ID AND DI.CGS_ID_1 = CD.ID AND DI.CGS_ID_2 = CD.ID

where DI.Priority = @priority

 group by CGS.Description, CD.VValue,  DI.Margin

Results

enter image description here

db_diagram

enter image description here

another example where i tried this query getting null values when including the Report table in stead of the DI table:

second result set

SELECT SUM(CD.VValue),  CGS.Description,
SUM(CD.VValue)

FROM CGS AS CGS

FULL JOIN ChannelParameter AS CP ON CP.ID = CGS.ID
FULL JOIN ChannelData AS CD ON CP.ID = CD.ID
FULL JOIN Report AS R on R.CGS_ID = CGS.ID

where -- CD.DDate BETWEEN @startDate AND @endDate 
             r.Description = 'rEGION EP'

 group by CGS.Description, CD.VValue'

Upvotes: 0

Views: 1044

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Sometimes, merly joining tables is not the solution. First of all, it is often recommended or even necessary to aggregate first and join the aggregate rather than first joining the tables and aggregate then.

Then, when we merely want to check whether values in a table exist, we usually want this as criteria in the WHERE clause, rather than in a join.

And sometimes we want to combine data sets with UNION ALL (or sometimes with UNION).

First query: The vvallue sum per CGS, margin and priority. Well, a CGS can have multiple margins and priorities, so we'll have to find these first. As the table in question has a CGS_ID_1 and a CGS_ID_2, we'll use UNION in order to get margins and priorities per CGS. Then, the vvalue has nothing to do with margin and priority; it's merely a sum per CGS, so it is going to be repeated in the results.

select cgs.description, di.margin, di.priority, coalesce(sum_vvalue, 0) as total
from cgs
join
(
  select cgs_id_1 as cgs_id, margin, priority from dataintegrity
  union
  select cgs_id_1 as cgs_id, margin, priority from dataintegrity
) di on di.cgs_id = cgs.id and di.priority = @priority
left join
(
  select id as cgs_id, sum(vvalue) as sum_vvalue
  from channeldata
  group by id
) cd on cd.cgs_id = cgs.id
order by cgs.description, di.margin, di.priority;

Secons query: The vvallue sum per CGS for a certain region. There can be many reports per CGS and we'll have to find all CGS with reports in the desired region. This is a mere lookup, which should be done with IN or EXISTS.

select cgs.description, coalesce(sum_vvalue, 0) as total
from cgs
left join
(
  select id as cgs_id, sum(vvalue) as sum_vvalue
  from channeldata
  group by id
) cd on cd.cgs_id = di.cgs_id
where cgs.id in (select cgs_id from report where description = 'REGION EP')
order by cgs.description;

Upvotes: 0

fauxmosapien
fauxmosapien

Reputation: 535

I'd guess the problem is in the join:

 LEFT JOIN ChannelData AS CD ON CP.ID = CD.ID AND DI.CGS_ID_1 = CD.ID AND DI.CGS_ID_2 = CD.ID

Elsewhere you're using a join on CGS_ID_1 OR CGS_ID_2, in this clause you're doing it with AND. Is this correct?

Also CP.ID = CD.ID looks suspect. It looks like a join from a primary key to a primary key instead of a primary key to a foreign key.

Upvotes: 1

Related Questions