Issue with max results from multiple tables

[DSaba10]

So I'm trying to pull data from a few tables which require a max function to pull the proper information out. The query I have works wonderfully except that there is a certain set of records it's not returning. Those records would be ones that are actually in the ProspectDeleteBlock table, but do not exist in the SegmentProspect table. I'd love to be able to include those records, but it seems like an inner join is created here from my left joins somehow which is causing only records that exist in both ProspectDeleteBlock AND SegmentProspect to be shown. I'm just not seeing the issue in my joins or my statements. Any help appreciated!

select distinct p.CycleID, sp.SegmentCode, RuleName, pc.HeliosKey, P.recordtype,
case
	when CellPhone is null then 'N' else 'Y' 
end CellPhone,
case 
	when BusinessPhone is null then 'N' else 'Y'
end BusPhone,
case 
	when EmailAddress is null then 'N' else 'Y'
end Email,
case 
	when BusinessEmail is null then 'N' else 'Y'
end Bemail,
ProspectPreferredGradYear,
LastGiftAmount, convert(char, LastGiftDate, 112) as LastGiftDate,
PhoneAppendOutcome, NCOAOutcome
from Prospect P
left outer join ProspectDeleteBlock pdb on pdb.ProspectCycleID = p.ProspectCycleID
inner join DeleteBlock db on pdb.DeleteBlockID = db.DeleteBlockID
inner join DeleteBlockRule dbr on db.DeleteBlockRuleID = dbr.DeleteBlockRuleID
inner join ProspectContainer pc on pc.ProspectID = p.ProspectID
left outer join SegmentProspect sp on p.ProspectCycleID = sp.ProspectCycleID
inner join Segment s on s.SegmentationID = sp.SegmentationID and sp.SegmentCode = s.SegmentCode
where p.CycleID =540
and Priority = (
	select MIN(priority) from ProspectDeleteBlock pdb1
	inner join DeleteBlock db1 on pdb1.DeleteBlockID = db1.DeleteBlockID  
	where pdb1.ProspectCycleID = pdb.ProspectCycleID
)
and SegmentRank = (
	select MIN(s1.SegmentRank) from SegmentProspect sp1
	inner join Segment s1 on s1.SegmentationID = sp1.SegmentationID and s1.SegmentCode = sp1.SegmentCode
	where sp1.ProspectCycleID = sp.ProspectCycleID
)
order by pc.HeliosKey

Thanks,
-DSaba

 

[philhege]

The joins aren't nested. Using an inner join whacks the whole record in the absence of a joinable record from the participant.

So, in Prospect I have CycleID 1.

IN ProspectDeleteBlock I don't have ID 1. So I have NULL values from ProspectDeleteBlock for this row.

Now I do an INNER JOIN to DeleteBlock based on the ID value in ProspectDeleteBlock. Oh, wait; it's NULL for this record. No join, no result. That rolls back up to the base table result.

Replace your inner joins with LEFT OUTER JOINs and see what happens.

-----------
With business clients like mine, you'd be better off herding cats.

 

[DSaba10]

I tried your solution, but it wasn't the issue. Here is a mock table structure hierarchy:

Prospect
SegmentProspect ProspectDeleteBlock
Segment DeleteBlock
DeleteBlockRule

The left outer joins connect the segmentprospect and prospectdeleteblock tables back to the main prospect table. The other three tables (segment, deleteblock, and deleteblockrule) are not affiliated with the Prospect table, and shouldn't require the left outer joins unless there are records in SegmentProspect and ProspectDeleteBlock that are not actually in those three.. (which isn't the case).

I feel the issue is somehow related to the fact that I have:

and SegmentRank = ...

I feel like this is creating the same type of situation as if I had an inner join on the SegmentProspect table. I've tried moving that whole section to be an additional condition of that left outer join:

left outer join SegmentProspect sp on pdb.ProspectCycleID = sp.ProspectCycleID
inner join Segment s on s.SegmentationID = sp.SegmentationID and sp.SegmentCode = s.SegmentCode
and SegmentRank = (
	select MIN(s1.SegmentRank) from SegmentProspect sp1
	inner join Segment s1 on s1.SegmentationID = sp1.SegmentationID and s1.SegmentCode = sp1.SegmentCode
	where sp1.ProspectCycleID = sp.ProspectCycleID
)

And that didn't work either.

When I remove references to anything involving the SegmentProspect table, I get all of the results I need, but I need to be able to report on results from that table as well. So then the SegmentRank line is creating that inner join because it's looking for all results where the rank actually has a value.. SO, I attempted to add in a line like this:

or SegmentRank is null

This didn't seem to do anything though.

DSaba

 

[DSaba10]

I've found a solution to my problem:

select distinct p.CycleID, isnull(d.SegmentCode, 'No Segment') SegmentCode, dbr.RuleName, pc.HeliosKey, P.recordtype,
case
	when CellPhone is null then 'N' else 'Y' 
end CellPhone,
case 
	when BusinessPhone is null then 'N' else 'Y'
end BusPhone,
case 
	when EmailAddress is null then 'N' else 'Y'
end Email,
case 
	when BusinessEmail is null then 'N' else 'Y'
end Bemail,
ProspectPreferredGradYear,
LastGiftAmount, convert(char, LastGiftDate, 112) as LastGiftDate,
PhoneAppendOutcome, NCOAOutcome
from Prospect P
inner join ProspectDeleteBlock pdb on pdb.ProspectCycleID = p.ProspectCycleID
inner join DeleteBlock db on pdb.DeleteBlockID = db.DeleteBlockID
inner join DeleteBlockRule dbr on db.DeleteBlockRuleID = dbr.DeleteBlockRuleID
inner join ProspectContainer pc on pc.ProspectID = p.ProspectID
[highlight #FCE94F]left join (
	select sp1.prospectcycleid, sp1.SegmentCode, sp1.ReportField2, ROW_NUMBER() over(partition by helioskey order by segmentrank ) rn from Prospect p1 
	inner join ProspectContainer pc1 on pc1.ProspectID = p1.ProspectID
	left join SegmentProspect sp1 on sp1.ProspectCycleID = p1.ProspectCycleID
	left join Segment s1 on s1.SegmentationID = sp1.SegmentationID and s1.SegmentCode = sp1.SegmentCode
	where CycleID = 540
) d on d.ProspectCycleID = p.ProspectCycleID and d.rn = 1[/highlight]
where p.CycleID =540
and Priority = (
	select MIN(priority) from ProspectDeleteBlock pdb1
	inner join DeleteBlock db1 on pdb1.DeleteBlockID = db1.DeleteBlockID  
	where pdb1.ProspectCycleID = pdb.ProspectCycleID
)
order by pc.HeliosKey

Thanks

DSaba