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
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
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 ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)