KcCzvBHZ

1DROP TABLE IF EXISTS [dbo].[Account];
2DROP TABLE IF EXISTS [dbo].[State];
3
4-- [Account] table and sample values.
5IF OBJECT_ID('[dbo].[Account]', 'U') IS NULL
6BEGIN
7    CREATE TABLE [dbo].[Account] (
8        [AccountId] [int] IDENTITY(1,1) NOT NULL
9        ,[AccountAlias] [varchar](3) NOT NULL
10        ,[AccountName] [varchar](128) NOT NULL
11        ,CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED ([AccountId] ASC)
12        ,CONSTRAINT [UQ_Account_Alias] UNIQUE NONCLUSTERED ([AccountAlias] ASC)
13        ,CONSTRAINT [UQ_Account_Name] UNIQUE NONCLUSTERED ([AccountName] ASC)
14    );
15
16    SET IDENTITY_INSERT [dbo].[Account] ON;
17
18    INSERT INTO [dbo].[Account] ([AccountId], [AccountAlias], [AccountName])
19    VALUES (1, 'SA1', 'Sample Account 1'), (2, 'SA2', 'Sample Account 2'), (3, 'SA3', 'Sample Account 3')
20
21    SET IDENTITY_INSERT [dbo].[Account] OFF;
22END;
23GO
24
25-- [State] table and sample values.
26IF OBJECT_ID('[dbo].[State]', 'U') IS NULL
27BEGIN
28    CREATE TABLE [dbo].[State] (
29        [StateId] [tinyint] IDENTITY(1,1) NOT NULL
30        ,[StateCode] [varchar](2) NOT NULL
31        ,[StateName] [varchar](32) NOT NULL
32        ,CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED ([StateId] ASC)
33        ,CONSTRAINT [UQ_State_Code] UNIQUE NONCLUSTERED ([StateCode] ASC)
34        ,CONSTRAINT [UQ_State_Name] UNIQUE NONCLUSTERED ([StateName] ASC)
35    );
36
37    SET IDENTITY_INSERT [dbo].[State] ON;
38
39    INSERT INTO [dbo].[State] ([StateId], [StateCode], [StateName])
40    VALUES (1, 'AL', 'Alabama'), (2, 'AK', 'Alaska'), (3, 'AZ', 'Arizona'), (4, 'AR', 'Arkansas'), (5, 'CA', 'California')
41
42    SET IDENTITY_INSERT [dbo].[State] OFF;
43END;
44GO 
45     
46DROP TABLE IF EXISTS [dbo].[AccountState];
47
48IF OBJECT_ID('[dbo].[AccountState]', 'U') IS NULL
49BEGIN
50    CREATE TABLE [dbo].[AccountState] (
51        [AccountId] [int] NOT NULL
52        ,[StateId] [tinyint] NOT NULL
53        ,CONSTRAINT [PK_AccountState] PRIMARY KEY CLUSTERED ([AccountId] ASC, [StateId] ASC)
54        ,CONSTRAINT [FK_AccountState_Account] FOREIGN KEY ([AccountId]) REFERENCES [dbo].[Account]([AccountId])
55        ,CONSTRAINT [FK_AccountState_State] FOREIGN KEY ([StateId]) REFERENCES [dbo].[State]([StateId])
56    );
57
58    INSERT INTO [dbo].[AccountState] ([AccountId], [StateId])
59    SELECT A.[AccountId], S.[StateId]
60    FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
61    WHERE A.[AccountId] = 1 AND S.[StateId] IN (1, 2, 3)
62    UNION
63    SELECT A.[AccountId], S.[StateId]
64    FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
65    WHERE A.[AccountId] = 2 AND S.[StateId] IN (3, 4, 5)
66    UNION
67    SELECT A.[AccountId], S.[StateId]
68    FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
69    WHERE A.[AccountId] = 3 AND S.[StateId] IN (1, 3, 5)
70END;
71GO 
72     
73DROP TABLE IF EXISTS [dbo].[AccountState];
74
75IF OBJECT_ID('[dbo].[AccountState]', 'U') IS NULL
76BEGIN
77    CREATE TABLE [dbo].[AccountState] (
78        [AccountId] [int] NOT NULL
79        ,[StateId] [tinyint] NOT NULL
80        ,CONSTRAINT [PK_AccountState] PRIMARY KEY CLUSTERED ([AccountId] ASC, [StateId] ASC)
81        ,CONSTRAINT [FK_AccountState_Account] FOREIGN KEY ([AccountId]) REFERENCES [dbo].[Account]([AccountId])
82        ,CONSTRAINT [FK_AccountState_State] FOREIGN KEY ([StateId]) REFERENCES [dbo].[State]([StateId])
83        ,INDEX [IX_AccountState_Account] NONCLUSTERED ([AccountId])
84        ,INDEX [IX_AccountState_State] NONCLUSTERED ([StateId])
85    );
86
87    INSERT INTO [dbo].[AccountState] ([AccountId], [StateId])
88    SELECT A.[AccountId], S.[StateId]
89    FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
90    WHERE A.[AccountId] = 1 AND S.[StateId] IN (1, 2, 3)
91    UNION
92    SELECT A.[AccountId], S.[StateId]
93    FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
94    WHERE A.[AccountId] = 2 AND S.[StateId] IN (3, 4, 5)
95    UNION
96    SELECT A.[AccountId], S.[StateId]
97    FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
98    WHERE A.[AccountId] = 3 AND S.[StateId] IN (1, 3, 5)
99END;
100GO 
101     
102SELECT
103    A.[AccountName]
104    ,S.[StateName]
105FROM
106    [dbo].[AccountState] A_S
107    JOIN [dbo].[Account] A
108        ON A_S.[AccountId] = A.[AccountId]
109    JOIN [dbo].[State] S
110        ON A_S.[StateId] = S.[StateId]
111WHERE
112    S.[StateCode] = 'CA'