joining tables

have a series tables I am joining for a union view, one of  the tables does not update with the id number unless the table is populated.

the id  column defaults to null, how can I by pass that to make the query work and get the results required
see attachedtest-view.docx
Amanda WalshawBusiness Solutions AnalsystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Which of the tables is the one giving the problem?
Can you not make that an 'identity' so it is populated?

please also note that

select ... union select ... union select ...

will produce the same result as:

select distinct ... union select distinct ... union select distinct ...

(the distinct's aren't needed)
0
PortletPaulfreelancerCommented:
without understanding your actual question I'm not sure what to propose for solving it - but I would suggest use of a CTE to simplify your overall query, like this: BUT test it first of course:
ALTER VIEW [dbo].[Test_view_vw]
AS
;WITH
 CTE AS (
            SELECT DISTINCT /* ideally this distinct would be removed */
                    a.myid                                            AS [My No]
                  , (a.lastname + ', ' + a.firstname + ' ' + a.Title) AS 'Deceased Names'
                  , b.PLAddress                                       AS [Address Line 1]
                  , b.city                                            AS [Address Line 2]
                  , b.name                                            AS [Trading Name]
                  , e.FADRES1                                         AS [Service Address Line 1]
                  , e.FADRES2                                         AS [Service City]
                  , e.FLOCATIONA                                      AS [Service Location]
                  , e.PRIVATESERVICE
                  , e.SDATE                                           AS [Service Date]
                  , e.STIME                                           AS [Service Time]
                  , e.WEB
                  , g.KNOWNAS                                         AS [Known As]
                  , l.MFADRES1                                        AS [Thanksgiving Address 1]
                  , l.MFADRES2                                        AS [Thanksgiving Suburb]
                  , l.MFLOCATIONA                                     AS [Thanksgiving Location]
                  , l.MSDATE                                          AS [Thanksgiving Date]
                  , l.MSTIME                                          AS [Thanksgiving Time]
                  , l.msprivate
                  , CASE
                                -- No details given Private Service not Ticked, Private Memorial Flag not Ticked, Webflag = 0
                        WHEN e.web = 0 AND e.PRIVATESERVICE = 0 AND l.MSPRIVATE = 0 THEN 1 END

                                -- No details given Private Service Ticked, Private Memorial Flag Not Ticked, Webflag = 0
                        WHEN e.web = 0 AND e.PRIVATESERVICE = 1  AND l.MSPRIVATE = 0 THEN 2 END

                                --  NO Details Given Private Service not Ticked, Private Memorial Flag Ticked, Webflag = 0
                        WHEN e.web = 0  AND e.PRIVATESERVICE = 0  AND l.MSPRIVATE = 1 THEN 3 END

                                --  NO Details Given Private Service is Ticked, Private Memorial Flag is Ticked, Webflag = 0
                        WHEN e.web = 0  AND e.PRIVATESERVICE = 1  AND l.MSPRIVATE = 1 THEN 4 END

                                --  NO Details Given Private Service is Ticked, Private Memorial Flag is Ticked, Webflag = 1
                        WHEN e.web = 1 AND e.PRIVATESERVICE = 1 AND l.MSPRIVATE = 1 THEN 5 END

                                --   Details Given Private Service is Ticked, Private Memorial Flag is not Ticked, Webflag = 1
                        WHEN e.web = 1  AND e.PRIVATESERVICE = 1  AND l.MSPRIVATE = 0 THEN 6 END

                                -- Details Given Private Service is not Ticked, Private Memorial Flag is Ticked, Webflag = 1
                        WHEN e.web = 1  AND e.PRIVATESERVICE = 1  AND l.MSPRIVATE = 0 THEN 7 END

                                --  Details Given Private Service is not Ticked, Private Memorial Flag is not Ticked, Webflag = 1
                        WHEN e.web = 1  AND e.PRIVATESERVICE = 0  AND l.MSPRIVATE = 0 THEN 8 END
                        ELSE 0
                    END AS flag
            FROM arrangement a
            JOIN Branch b
                    ON a.Branch = b.branchid
            LEFT OUTER JOIN employee c
                    ON a.arranger = c.employeeid
            JOIN deathtable d
                    ON a.myid = d.myid
            JOIN servicetable e
                    ON a.myid = e.myid
            JOIN fduserpersonal g
                    ON g.myid = a.myid
            INNER JOIN memorialtable l
                    ON l.myid = e.myid
            WHERE (
                   e.SDATE >= DATEADD(dd, -5, GETDATE() )
                  OR
                    l.MSDATE >= DATEADD(dd, -5, GETDATE() )
                  )
        ) /* end CTE */
SELECT
        [My No]
      , 1                         AS [PRIVATE]
      , [Trading Name]
      , [Address Line 1]
      , [Address Line 2]
      , 'Deceased Names'
      , [Known As]
      , ''                        AS [Service Date]
      , ''                        AS [Service Time]
      , ''                        AS [Service Location]
      , ''                        AS [Service Address Line 1]
      , ''                        AS [Service Address Line 2]
      , ''                        AS [Service City]
      , ''                        AS [Thanksgiving Date]
      , ''                        AS [Thanksgiving Time]
      , ''                        AS [Thanksgiving Location]
      , ''                        AS [Thanksgiving Address 1]
      , ''                        AS [Thanksgiving Address 2]
      , ''                        AS [Thanksgiving Suburb]
      , e.web
      , e.PRIVATESERVICE
      , l.msprivate
FROM CTE
WHERE flag = 1
UNION ALL
SELECT
        [My No]
      , 1                         AS [PRIVATE]
      , [Trading Name]
      , [Address Line 1]
      , [Address Line 2]
      , 'Deceased Names'
      , [Known As]
      , ''                        AS [Service Date]
      , ''                        AS [Service Time]
      , ''                        AS [Service Location]
      , ''                        AS [Service Address Line 1]
      , ''                        AS [Service Address Line 2]
      , ''                        AS [Service City]
      , ''                        AS [Thanksgiving Date]
      , ''                        AS [Thanksgiving Time]
      , ''                        AS [Thanksgiving Location]
      , ''                        AS [Thanksgiving Address 1]
      , ''                        AS [Thanksgiving Address 2]
      , ''                        AS [Thanksgiving Suburb]
      , e.WEB
      , e.PRIVATESERVICE
      , l.msprivate
FROM CTE
WHERE flag = 2
UNION ALL
SELECT
        [My No]
      , 1                         AS [PRIVATE]
      , [Trading Name]
      , [Address Line 1]
      , [Address Line 2]
      , 'Deceased Names'
      , [Known As]
      , ''                        AS [Service Date]
      , ''                        AS [Service Time]
      , ''                        AS [Service Location]
      , ''                        AS [Service Address Line 1]
      , ''                        AS [Service Address Line 2]
      , ''                        AS [Service City]
      , ''                        AS [Thanksgiving Date]
      , ''                        AS [Thanksgiving Time]
      , ''                        AS [Thanksgiving Location]
      , ''                        AS [Thanksgiving Address 1]
      , ''                        AS [Thanksgiving Address 2]
      , ''                        AS [Thanksgiving Suburb]
      , e.web
      , e.PRIVATESERVICE
      , l.msprivate
FROM CTE
WHERE flag = 3
UNION ALL
SELECT
        [My No]
      , 1                         AS [PRIVATE]
      , [Trading Name]
      , [Address Line 1]
      , [Address Line 2]
      , 'Deceased Names'
      , [Known As]
      , ''                        AS [Service Date]
      , ''                        AS [Service Time]
      , ''                        AS [Service Location]
      , ''                        AS [Service Address Line 1]
      , ''                        AS [Service Address Line 2]
      , ''                        AS [Service City]
      , ''                        AS [Thanksgiving Date]
      , ''                        AS [Thanksgiving Time]
      , ''                        AS [Thanksgiving Location]
      , ''                        AS [Thanksgiving Address 1]
      , ''                        AS [Thanksgiving Address 2]
      , ''                        AS [Thanksgiving Suburb]
      , e.web
      , e.PRIVATESERVICE
      , l.msprivate
FROM CTE
WHERE flag = 4
UNION ALL
SELECT
        [My No]
      , 1                         AS [PRIVATE]
      , [Trading Name]
      , [Address Line 1]
      , [Address Line 2]
      , 'Deceased Names'
      , [Known As]
      , ''                        AS [Service Date]
      , ''                        AS [Service Time]
      , ''                        AS [Service Location]
      , ''                        AS [Service Address Line 1]
      , ''                        AS [Service Address Line 2]
      , ''                        AS [Service City]
      , ''                        AS [Thanksgiving Date]
      , ''                        AS [Thanksgiving Time]
      , ''                        AS [Thanksgiving Location]
      , ''                        AS [Thanksgiving Address 1]
      , ''                        AS [Thanksgiving Address 2]
      , ''                        AS [Thanksgiving Suburb]
      , e.web
      , e.PRIVATESERVICE
      , l.msprivate
FROM CTE
WHERE flag = 5
UNION ALL
SELECT
        [My No]
      , 0                         AS [PRIVATE]
      , [Trading Name]
      , [Address Line 1]
      , [Address Line 2]
      , 'Deceased Names'
      , [Known As]
      , ''                        AS [Service Date]
      , ''                        AS [Service Time]
      , ''                        AS [Service Location]
      , ''                        AS [Service Address Line 1]
      , ''                        AS [Service Address Line 2]
      , ''                        AS [Service City]
      , [Thanksgiving Date]
      , [Thanksgiving Time]
      , [Thanksgiving Location]
      , [Thanksgiving Address 1]
      , ''                        AS [Thanksgiving Address 2]
      , [Thanksgiving Suburb]
      , e.web
      , e.PRIVATESERVICE
      , l.msprivate
FROM CTE
WHERE flag = 6
UNION ALL
SELECT
        [My No]
      , 0                         AS [PRIVATE]
      , [Trading Name]
      , [Address Line 1]
      , [Address Line 2]
      , 'Deceased Names'
      , [Known As]
      , [Service Date]
      , [Service Time]
      , [Service Location]
      , [Service Address Line 1]
      , ''                        AS [Service Address Line 2]
      , [Service City]
      , ''                        AS [Thanksgiving Date]
      , ''                        AS [Thanksgiving Time]
      , ''                        AS [Thanksgiving Location]
      , ''                        AS [Thanksgiving Address 1]
      , ''                        AS [Thanksgiving Address 2]
      , ''                        AS [Thanksgiving Suburb]
      , e.web
      , e.PRIVATESERVICE
      , l.msprivate
FROM CTE
WHERE flag = 7
UNION ALL
SELECT
        [My No]
      , 1                        AS [PRIVATE]
      , [Trading Name]
      , [Address Line 1]
      , [Address Line 2]
      , 'Deceased Names'
      , [Known As]
      , [Service Date]
      , [Service Time]
      , [Service Location]
      , [Service Address Line 1]
      , [Service Address Line 2]
      , [Service City]
      , [Thanksgiving Date]
      , [Thanksgiving Time]
      , [Thanksgiving Location]
      , [Thanksgiving Address 1]
      , [Thanksgiving Address 2]
      , [Thanksgiving Suburb]
      , e.web
      , e.PRIVATESERVICE
      , l.msprivate
FROM CTE
WHERE flag = 8
	
GO	

Open in new window

0
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
Hi Paul
Thanks for the suggestion, the table that is giving me issues is mymemorial table, sorry I should be mentioned that before.  When a new case is created but there are no memorial details entered because there was not one, it leaves myid number as null.

Only when  is a memorial entered I will get all results.

I am looking for if null then
and what I need to say is
case when l.MFLOCATIONA is null then '' else l.mflocationa end as [Thanksgiving Location],
  case when l.MFADRES1  is null then '' else l.MFADRES1 end as [Thanksgiving Address 1],
    case when l.MFADRES2 IS NULL  then '' else l.MFADRES2 end  AS [Thanksgiving Suburb],

THAT SHOULD WORK
0
PortletPaulfreelancerCommented:
OK, those details definitely do help

I'm going to assume you will adopt my CTE suggestion (also assuming it produces the same result)

For ALL of the fields from alias l. apply ISNULL() - lines 19-24 above: but take care with teh data types e.g. dates integers etc.
                  , ISNULL(l.MFADRES1,'')                             AS [Thanksgiving Address 1]
                  , ISNULL(l.MFADRES2,'')                             AS [Thanksgiving Suburb]
                  , ISNULL(l.MFLOCATIONA,'')                          AS [Thanksgiving Location]
                  , ISNULL(l.MSDATE,'')                               AS [Thanksgiving Date]
                  , ISNULL(l.MSTIME,'')                               AS [Thanksgiving Time]
                  , ISNULL(l.msprivate,??????????????)                            AS msprivate

Open in new window

BUT... what do you want to do with the conditions that are based on msprivate?
should that default to zero - this affects the case expression too and all references to msprivate within the CTE should use ISNULL()

if this is unclear let me know please.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.