December 23, 2014 at 12:47 pm
Hi,
I need to write view statement.
I have a table A, Table B and Table C
Table A has columns (id, address1, adress2, city, zipcode, state, country)
Table B has (id, email, address_id, phone no, phone type)
Table C has (id, firstname, lastname, dob, locationid, contactinformation)
I have the requirement to write view
when id in Table A
when id in (select distinct address_id from TableB join Table C on B.id=C.contactinformation) then I have to mask Table A columns except id, else I have to display as it is
Any help would be appreciated
December 23, 2014 at 1:13 pm
Looks like the usage of OUTER APPLY and the CASE expression will help here.
Depends on the query itself. Not enough information from my point of view...
December 23, 2014 at 1:26 pm
really helps to have that ready to go ddl so we can test solutions. here's a query I like, might not have the efficiency of "apply" methods and of course haven't tested it either, and for that matter might not even do what you want 🙂
with selectors as
(
select 2 which_one, id, address1, address2, city, zipcode, state, country
from table_a
union
select 1 which_one, id, '' address1, '' address2, '' city, '' zipcode, '' state, '' country
from table_b tb join table_c tc on tb.id = tc.id
)
select selected, id, address1, address2, city,zipcode, state, country
from
(
select id id_selected, min(which_one) selected from selectors group by id
) selecting
join
selectors
on id_selected = id and selected = which_one
edit: fixed obvious error
December 26, 2014 at 11:12 am
SELECT a.id,
CASE WHEN match_found.address_id IS NULL THEN '' ELSE address1 END AS address1,
CASE WHEN match_found.address_id IS NULL THEN '' ELSE address2 END AS address2
--,...other a. columns--
FROM tableA a
OUTER APPLY (
SELECT TOP (1) b.address_id
FROM tableB b
WHERE b.address_id = a.id
AND EXISTS(
SELECT 1
FROM tableC c
WHERE c.contactinformation = b.id
)
) AS match_found
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply