Geek asks and answers

The best way to check the status instead of repeating the case. When

These are my tables:

[Member]: 
{ [Id], [UserId], [UserName], [Email], [Status_Id], [MemberType_Id] }

[CustomerProfile] :
{ [Id], [Member_Id], [FirstName], [LastName], [SSN], [Gender], [PostalCode],[City_Id], [Address]}


[DealerProfile]:
{ [Id], [Member_Id], [FirstName], [LastName], [SSN], [Gender], [StoreName], [PostalCode], [City_Id], [Address] }


[ManagerProfile]
{ [Id], [Member_Id], [FirstName], [LastName], [SSN], [Gender] }

[City]:
{ [Id], [Title], [Province_Id] }

So, I need to create a complete view of all my members and related columns, I will start with the following select query:

SELECT     
[ME].[Id] AS [MemberId],
[ME].[UserId],
[ME].[UserName],
[ME].[Email],
[ME].[Status_Id],
[ST].[Title] AS [Status],
[ME].[MemberType_Id],
[MT].[Title] AS [MemberType],

CASE
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[Id] 
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[Id]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN [MAP].[Id] 
    ELSE 0 END AS [Profile_Id],
CASE
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[FirstName]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[FirstName]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN [MAP].[FirstName]
    ELSE 'Unknown' END AS [FirstName],
CASE
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[LastName]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[LastName]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN [MAP].[LastName]
    ELSE 'Unknown' END AS [LastName],
CASE
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[SSN]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[SSN]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN [MAP].[SSN]
    ELSE 'Unknown' END AS [SSN],
CASE
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[Address]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[Address]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN ''
    ELSE 'Unknown' END AS [Address],
CASE
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[PostalCode]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[PostalCode]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN ''
    ELSE 'Unknown' END AS [PostalCode],
CASE
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[Gender]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[Gender]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN [MAP].[Gender]
    ELSE 'Unknown' END AS [Gender],
CASE
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[City_Id]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[City_Id]
    WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN 0
    ELSE 0 END AS [CityId]

FROM [Members].[Member] AS [ME]
INNER JOIN [Members].[MemberType] AS [MT] ON [ME].[MemberType_Id] = [MT].[Id]
INNER JOIN [Members].[Status] AS [ST] ON [ME].[Status_Id]=[ST].[Id]
INNER JOIN [Members].[ManagerProfile] AS [MAP] ON [ME].[Id]= [MAP].[Member_Id]
INNER JOIN [Members].[CustomerProfile] AS [CUP] ON [ME].[Id]=[CUP].[Member_Id]
INNER JOIN [Members].[DealerProfile] AS [DEP] ON [ME].[Id]=[DEP].[Member_Id];
GO

As you can see, the case is repeated when you need to find the type of a member and decide to select a value from this table. Is it a good idea to create a presentation such as my opinion? Is there a better way to check member type? I should mention that the Id column in the table MemberTypecan be changed, so I do not want to use something like this: WHEN [ME].[MemberType_Id] = 1 Then ''what is your suggestion?

+3
source share
4 answers

I think it will help you.

SELECT     
[ME].[Id] AS [MemberId],
[ME].[UserId],
[ME].[UserName],
[ME].[Email],
[ME].[Status_Id],
[ST].[Title] AS [Status],
[ME].[MemberType_Id],
[MT].[Title] AS [MemberType],
[X].[Id] AS [Profile_Id], 
[X].[FirstName],
[X].[LastName],
[X].[SSN],
[X].[Address],
[X].[PostalCode],
[X].[Gender],
[X].[City_Id]
FROM [Members].[Member] AS [ME]
INNER JOIN [Members].[MemberType] AS [MT] ON [ME].[MemberType_Id] = [MT].[Id]
INNER JOIN [Members].[Status] AS [ST] ON [ME].[Status_Id]=[ST].[Id]
INNER JOIN 
    (Select [Member_Id], [Id], [FirstName], [LastName], [SSN], [Address]='', [PostalCode]='', [Gender], [City_Id]=0,
        [TYPE] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') 
        From [Members].[ManagerProfile] AS [MAP] Where [MAP].[Member_Id] = [ME].[Id]
    Union All
    Select [Member_Id], [Id], [FirstName], [LastName], [SSN], [Address], [PostalCode], [Gender], [City_Id], 
        [TYPE] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') 
    From [Members].[CustomerProfile] AS [CUP] Where [CUP].[Member_Id] = [ME].[Id]
    Union All
    Select [Member_Id], [Id], [FirstName], [LastName], [SSN], [Address], [PostalCode], [Gender], [City_Id], 
        [TYPE] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') 
    From [Members].[DealerProfile] AS [DEP] Where [DEP].[Member_Id] = [ME].[Id])
     AS [X] On [ME].[Id] = [X].[Member_Id] AND [ME].[MemberType_Id] = [X].[TYPE]
 GO
+1
source

, , , :

SELECT     
    [ME].[Id] AS [MemberId],
    [ME].[UserId],
    [ME].[UserName],
    [ME].[Email],
    [ME].[Status_Id],
    [ST].[Title] AS [Status],
    [ME].[MemberType_Id],
    [MT].[Title] AS [MemberType],
    a.ID,
    a.FirstName,
    a.LastName,
    a.SSN,
    a.Address,
    a.PostalCode,
    a.Gender,
    a.City_Id
FROM [Members].[Member] AS [ME]
INNER JOIN [Members].[MemberType] AS [MT] ON [ME].[MemberType_Id] = [MT].[Id]
INNER JOIN [Members].[Status] AS [ST] ON [ME].[Status_Id]=[ST].[Id]
INNER JOIN 
(
   select 'Manager' MemberType, Member_id, Firstname, LastName, SSN, 
          Address, PostalCode, Gender, City_id
     from ManagerProfile
   union all
   select 'Customer', Member_id, Firstname, LastName, SSN, 
          Address, PostalCode, Gender, City_id
     from CustomerProfile
   union all
   select 'Dealer', Member_id, Firstname, LastName, SSN, 
          '', '', Gender, 0
     from DealerProfile
)   
   on me.id = a.member_id
  and MT.TYPE = a.MemberType

, , , , . MemberTypeId .

+1

what about it:

CASE
    WHEN [MT].[Title]='Customer' THEN [CUP].[FirstName]
    WHEN [MT].[Title]='Dealer' THEN [DEP].[FirstName]
    WHEN [MT].[Title]='Manager' THEN [MAP].[FirstName]
    ELSE 'Unknown' END AS [FirstName]
0
source

You can also CROSS JOINwith types. You will still be comparing the instructions typid, and the instructions casewill be much easier to read. Like this:

SELECT     
[ME].[Id] AS [MemberId],
[ME].[UserId],
[ME].[UserName],
[ME].[Email],
[ME].[Status_Id],
[ST].[Title] AS [Status],
[ME].[MemberType_Id],
[MT].[Title] AS [MemberType],

CASE
    WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[Id] 
    WHEN [ME].[MemberType_Id] = tblTypes.DealerId THEN [DEP].[Id]
    WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN [MAP].[Id] 
    ELSE 0 END AS [Profile_Id],
CASE
    WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[FirstName]
    WHEN [ME].[MemberType_Id] = (tblTypes.DealerId  THEN [DEP].[FirstName]
    WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN [MAP].[FirstName]
    ELSE 'Unknown' END AS [FirstName],
CASE
    WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[LastName]
    WHEN [ME].[MemberType_Id] = tblTypes.DealerId  THEN [DEP].[LastName]
    WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN [MAP].[LastName]
    ELSE 'Unknown' END AS [LastName],
CASE
    WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[SSN]
    WHEN [ME].[MemberType_Id] = tblTypes.DealerId  THEN [DEP].[SSN]
    WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN [MAP].[SSN]
    ELSE 'Unknown' END AS [SSN],
CASE
    WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[Address]
    WHEN [ME].[MemberType_Id] = tblTypes.DealerId  THEN [DEP].[Address]
    WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN ''
    ELSE 'Unknown' END AS [Address],
CASE
    WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[PostalCode]
    WHEN [ME].[MemberType_Id] = tblTypes.DealerId  THEN [DEP].[PostalCode]
    WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN ''
    ELSE 'Unknown' END AS [PostalCode],
CASE
    WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[Gender]
    WHEN [ME].[MemberType_Id] = tblTypes.DealerId  THEN [DEP].[Gender]
    WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN [MAP].[Gender]
    ELSE 'Unknown' END AS [Gender],
CASE
    WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[City_Id]
    WHEN [ME].[MemberType_Id] = tblTypes.DealerId  THEN [DEP].[City_Id]
    WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN 0
    ELSE 0 END AS [CityId]

FROM [Members].[Member] AS [ME]
INNER JOIN [Members].[MemberType] AS [MT] ON [ME].[MemberType_Id] = [MT].[Id]
INNER JOIN [Members].[Status] AS [ST] ON [ME].[Status_Id]=[ST].[Id]
INNER JOIN [Members].[ManagerProfile] AS [MAP] ON [ME].[Id]= [MAP].[Member_Id]
INNER JOIN [Members].[CustomerProfile] AS [CUP] ON [ME].[Id]=[CUP].[Member_Id]
INNER JOIN [Members].[DealerProfile] AS [DEP] ON [ME].[Id]=[DEP].[Member_Id]
CROSS JOIN
    (
        SELECT
            CustomerId,
            DealerId,
            ManagerId
        FROM
            (
                SELECT 1 AS nbr
            ) AS tblType
        LEFT JOIN
            (
                SELECT TOP(1) 
                    [TMT].[Id]  AS CustomerId
                FROM 
                    [Members].[MemberType] AS [TMT] 
                WHERE 
                    [TMT].[Title]='Customer'
            ) AS Customer ON 1=1
        LEFT JOIN
            (
                SELECT TOP(1) 
                    [TMT].[Id]  AS DealerId
                FROM 
                    [Members].[MemberType] AS [TMT] 
                WHERE 
                    [TMT].[Title]='Dealer'
            ) AS Dealer ON 1=1
        LEFT JOIN
            (
                SELECT TOP(1) 
                    [TMT].[Id]  AS ManagerId
                FROM 
                    [Members].[MemberType] AS [TMT] 
                WHERE 
                    [TMT].[Title]='Manager'
            ) AS Manager ON 1=1
    ) AS tblTypes
0
source

More articles:


All Articles