Select all from primary table, join to others

Question

I am hoping somebody can help me. If I do a standard Inner Join or right join, i get very few results. Would also be great to also know why what I am trying wont work.

I have a primary Table of members. MemberID is common to all tables

Select * from apf_members

MemberID--|--AppsTitle--|--AppsFirstName--|--AppsLastName--

2015       Mrs             Naomi            Specter 
2016       Mr              Marisa           Watson 
2025       Mr              Elia             Barker 
2031       Dr              Heth             Rowing 
2044       Ms              Kathryn          McKenzie

I also want to attached 3 extra columns that are dynamically built.

--CurrentMember--|--UnrenewedMember--|--LapsedMember--

The queries below will currently return the value only ~IF~ the user has a record. where I need it to return a NULL or an empty string.

@theDate is a variable, ill assign it to Getdate for now.

Declare @theDate date
SET @theDate = GetDate()

Select Description as 'CurrentMember' from apf_finances 
where @theDate between StartDate and EndDate
and Status = 'Financial Status'


Select Description as 'UnrenewedMember'
from apf_finances 
where @theDate between DATEADD(year, -1, StartDate) and DATEADD(year, -1, EndDate)
and Status = 'Financial Status'


Select Description as 'LapsedMember'
from apf_finances 
where @theDate between DATEADD(year, -2, StartDate) and DATEADD(year, -2, EndDate)
and Status = 'Financial Status'

Put together,final result would look like this.

MemberID--|--AppsTitle--|--AppsFirstName--|--AppsLastName--|--CurrentMember--|--UnrenewedMember--|--LapsedMember--
2015       Mrs             Naomi            Specter          f                  nf                  nf
2016       Mr              Marisa           Watson           uf                 NULL                nf
2025       Mr              Elia             Barker          NULL                NULL                NULL
2031       Dr              Heth             Rowing          co                  exp                 f
2044       Ms              Kathryn          McKenzie        NULL                f                   NULL

*UPDATE 22/10/2013 *

here is a SQL script that should re-create the tables

CREATE TABLE [dbo].[apf_Members](
    [MemberID] [int] IDENTITY(1,1) NOT NULL,
    [AppsTitle] [nvarchar](50) NULL,
    [AppsFirstName] [nvarchar](50) NULL,
    [AppsMiddleName] [nvarchar](50) NULL,
    [AppsLastName] [nvarchar](50) NULL,

)

GO

CREATE TABLE [dbo].[apf_Finances](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [MemberID] [int] NOT NULL,
    [Description] [nvarchar](50) NULL,
    [StartDate] [date] NULL,
    [EndDate] [date] NULL,
    [Status] [nvarchar](50) NULL

)

GO

SET IDENTITY_INSERT dbo.[apf_Members] ON
GO

SET IDENTITY_INSERT dbo.[apf_Finances] ON
GO


INSERT INTO [dbo].[apf_Members]
           ([MemberID],[AppsTitle],[AppsFirstName],[AppsLastName])
           VALUES     (2015,'Mrs','Naomi', 'Specter' ),

(2016    ,   'Mr'  ,'Marisa','Watson' ),
(2025   ,    'Mr' ,'Elia','Barker'),
(2031  ,     'Dr','Heth','Rowing'),
(2044 ,      'Ms','Kathryn','McKenzie');

INSERT INTO [apf_Finances] 
    ([ID], [MemberID], [Description], [StartDate], [EndDate], [Status] )
    VALUES


(12381, 2016,   'f' ,'2013-10-15','2014-10-14','Financial Status'),
(12382, 2016,   ''  ,'2013-10-15','2014-10-14','Donation'),
(12361, 2025,   'f' ,'2013-10-12','2014-10-11','Financial Status'),
(12362, 2025,   ''  ,'2013-10-12','2014-10-11','Donation'),
(12357, 2031,   'f' ,'2013-10-11','2014-10-10','Financial Status'),
(12358, 2031,   ''  ,'2013-10-11','2014-10-10','Donation'),
(12379, 2044,   'f' ,'2012-10-21','2013-10-20','Financial Status'),
(12380, 2044,   ''  ,'2012-10-21','2013-10-20','Donation'),
(12377, 2016,   'f' ,'2012-10-17','2013-10-16','Financial Status'),
(12378, 2016,   ''  ,'2012-10-17','2013-10-16','Donation'),
(12373, 2025,   'f' ,'2012-10-16','2013-10-15','Financial Status'),
(12374, 2031,   ''  ,'2011-10-16','2013-10-15','Donation'),
(12375, 2031,   'f' ,'2011-10-16','2013-10-15','Financial Status'),
(12376, 2044,   ''  ,'2011-10-16','2013-10-15','Donation'),
(12371, 2044,   'f' ,'2011-10-15','2013-10-14','Financial Status');

Answer 1

Here is a Northwind example.

You can "filter" by using derived tables. And then left join on the derived table(s).

You can write yours, and you'll have 3 derived tables.

derivedCurrentMember derivedUnrenewedMember derivedLapsedMember

Put your filtering/logic in each derived table. Then left join on each one.

Here is a generic Northwind example.

My "filtering logic" is whether a discount existed or not. (innerOd1.Discount <=0 and innerOd1.Discount > 0). You may not need the group-by.

Use Northwind
GO


Select ords.OrderID 
 , ISNULL ( derived1.MyCount , 0)  as NoDiscountCount
 , ISNULL ( derived2.MyCount , 0)  as HasDiscountCount
from dbo.Orders ords

left join
( select innerOd1.OrderID , count (*) as MyCount from dbo.[Order Details] innerOd1 where innerOd1.Discount <=0 group by innerOd1.OrderID) derived1
on ords.OrderID = derived1.OrderID

left join
( select innerOd2.OrderID , count (*) as MyCount from dbo.[Order Details] innerOd2 where innerOd2.Discount > 0 group by innerOd2.OrderID) derived2
on ords.OrderID = derived2.OrderID

order by ords.OrderID 

::: APPEND :::

Here is the first one. You can fill in the rest.

declare @theDate datetime
select @theDate = getdate()


select membs.* , currentMemberDerived.[CurrentMember]  from
[dbo].[apf_Members] membs
left join 
(
Select MemberID , [Description] as 'CurrentMember' from apf_finances 
where @theDate between StartDate and EndDate
and Status = 'Financial Status'
) currentMemberDerived
on membs.MemberID = currentMemberDerived.MemberID