July 2, 2018 at 3:42 pm
Greetings to all members.
Here is my case and I could use some help here:
Table A holds a relationship between application and account.
An application can have one or many accounts belonging to the same customer. An account can also have multiple applications.
Table B holds a relationship between account and customer.
An account can have only one customer, but a customer can have many accounts.
I need a query to combine in one result set:
For all customers having an account in Table B that is not registered in Table A (an application must include all customer's accounts)
A) app_id, app_acc, customer_id columns plus a flag 'missing account' set to '1'
For all applications in Table A that have accounts that belong to different customers:
B) app_id, app_acc, customer_id columns plus a flag 'multiple accounts' set to '1'
For all customers having an account in Table B that is not registered in Table A AND For all applications in Table A that have accounts that belong to different customers:
C) app_id, app_acc, customer_id columns with both flags set to '1'
Thank you in advance for any assistance.
July 3, 2018 at 6:50 am
Sounds like a left join and Case when will do the trick. Without any real data and expected results, it's hard to give you an answer.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 3, 2018 at 1:36 pm
Hello Mike and thank you for your time.
I have attached an excel file with some data and the expected result.
Every help is highly appreciated.🙂
July 3, 2018 at 1:41 pm
pmyt - Tuesday, July 3, 2018 1:36 PMHello Mike and thank you for your time.
I have attached an excel file with some data and the expected result.Every help is highly appreciated.🙂
You really need to read and follow this: http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 3, 2018 at 3:03 pm
@Lynn Pettis
Apologies for any inconvenience!
Let's get it right this time...
USE [DBNAME HERE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE TableB(
[Per_id] [int] IDENTITY(1,1) NOT NULL,
[App_acc] [nvarchar](16) NOT NULL,
[CID] [nvarchar](10) NOT NULL,
CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED
(
[Per_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE TableA(
[Apacid] [int] IDENTITY(1,1) NOT NULL,
[App_id] [int] NOT NULL,
[App_acc] [nvarchar](16) NOT NULL,
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED
(
[Apacid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT TableB ON
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (1, N'1000000000000000', N'0000000001')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (2, N'2000000000000000', N'0000000002')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (3, N'3000000000000000', N'0000000003')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (4, N'4000000000000000', N'0000000004')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (5, N'5000000000000000', N'0000000005')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (6, N'6000000000000000', N'0000000006')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (7, N'7000000000000000', N'0000000007')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (8, N'8000000000000000', N'0000000008')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (9, N'9000000000000000', N'0000000009')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (10, N'1100000000000000', N'0000000001')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (11, N'1200000000000000', N'0000000001')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (12, N'1300000000000000', N'0000000003')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (13, N'1400000000000000', N'0000000001')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (14, N'1500000000000000', N'0000000010')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (15, N'1600000000000000', N'0000000010')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (16, N'1700000000000000', N'0000000011')
INSERT TableB ([Per_id], [App_acc], [CID]) VALUES (17, N'1800000000000000', N'0000000011')
SET IDENTITY_INSERT TableB OFF
SET IDENTITY_INSERT TableA ON
INSERT TableA ([Apacid], [App_id], [App_acc]) VALUES (1, 1, N'1000000000000000')
INSERT TableA ([Apacid], [App_id], [App_acc]) VALUES (2, 1, N'1100000000000000')
INSERT TableA ([Apacid], [App_id], [App_acc]) VALUES (3, 2, N'2000000000000000')
INSERT TableA ([Apacid], [App_id], [App_acc]) VALUES (4, 3, N'3000000000000000')
INSERT TableA ([Apacid], [App_id], [App_acc]) VALUES (5, 4, N'4000000000000000')
INSERT TableA ([Apacid], [App_id], [App_acc]) VALUES (6, 4, N'5000000000000000')
INSERT TableA ([Apacid], [App_id], [App_acc]) VALUES (7, 5, N'5000000000000000')
INSERT TableA ([Apacid], [App_id], [App_acc]) VALUES (8, 5, N'6000000000000000')
INSERT TableA ([Apacid], [App_id], [App_acc]) VALUES (9, 6, N'1000000000000000')
INSERT TableA ([Apacid], [App_id], [App_acc]) VALUES (10, 7, N'1500000000000000')
INSERT TableA ([Apacid], [App_id], [App_acc]) VALUES (11, 7, N'1700000000000000')
SET IDENTITY_INSERT TableA OFF
You can find the expected results in "Results.jpg"
Thank you
July 4, 2018 at 2:22 pm
pmyt - Monday, July 2, 2018 3:42 PMGreetings to all members.
Here is my case and I could use some help here:
Table A holds a relationship between application and account.
An application can have one or many accounts belonging to the same customer. An account can also have multiple applications.
Table B holds a relationship between account and customer.
An account can have only one customer, but a customer can have many accounts.
I need a query to combine in one result set:
For all customers having an account in Table B that is not registered in Table A (an application must include all customer's accounts)
A) app_id, app_acc, customer_id columns plus a flag 'missing account' set to '1'
For all applications in Table A that have accounts that belong to different customers:
B) app_id, app_acc, customer_id columns plus a flag 'multiple accounts' set to '1'
For all customers having an account in Table B that is not registered in Table A AND For all applications in Table A that have accounts that belong to different customers:
C) app_id, app_acc, customer_id columns with both flags set to '1'Thank you in advance for any assistance.
Why did you fail to post any DDL? Does your teacher or boss make you program from a vague narrative with serious errors in it? Let’s start making guesses about everything from the table names, the datatypes the constraints and everything else.
>> Customer_Applications holds a relationship between application and account.
An application can have one or many accounts belonging to the same customer. An account can also have multiple applications. <<
Why do you think that “Customer_Applications†is a valid name for a table? Why do you think it’s helpful? If you’re going to do this for a living, and you want to get in the habit of thinking about table names and how they can actually be useful to the next guy that has to maintain your code
CREATE TABLE Applications
(app_id CHAR(15) NOT NULL PRIMARY KEY,
..);
CREATE TABLE Accounts
(acct_nbr CHAR(10) NOT NULL PRIMARY KEY,
..);
>> Application_Accounts holds a relationship between application and account.
An application can have one or many accounts belonging to the same customer. An account can also have multiple applications.
<<
Here is the standard idiom for modeling a many to many relationship in SQL. Please look at the use of the reference clauses in the DDL.
CREATE TABLE Application_Accounts
(app_id CHAR(15) NOT NULL
REFERENCES Applications(app_id)
ON DELETE CASCADE,
acct_nbr CHAR(10) NOT NULL
REFERENCES Accounts(acct_nbr)
ON DELETE CASCADE,
PRIMARY KEY (app_id, acct_nbr), ..);
>> Customer_Accounts holds a relationship between account and customer.
An account can have only one customer, but a customer can have many accounts. <<
This is a one to many relationship. Notice the difference on what becomes the primary key.
CREATE TABLE Customers
(cust_id CHAR(10) NOT NULL PRIMARY KEY,
..);
CREATE TABLE Customer_Accounts
(cust_id CHAR(15) NOT NULL
REFERENCES Customer(cust_id)
ON DELETE CASCADE,
acct_nbr CHAR(10) NOT NULL PRIMARY KEY
REFERENCES Accounts(acct_nbr)
ON DELETE CASCADE,
..);
>> For all customers having an account in Customer_Accounts that is not registered in Customer_Applications (an application must include all customer's accounts) <<
A) app_id, app_acc, customer_id columns plus a flag 'missing account' set to '1'<<
We do not use flags in SQL. That was assembly language programming. Please read https://www.red-gate.com/simple-talk/sql/t-sql-programming/bit-of-a-problem/
>> For all applications in Application_Accounts that have accounts that belong to different customers: <<
I read this is an application with more than one customer. However, it might have been read as applications that do not belong to some customer X
SELECT AA.app_id AS multi_cust_app
FROM Application_Accounts AS AA,
Customer_Accounts AS CA
WHERE AA.acct_nbr = CA.acct_nbr
GROUP BY AA.app_id
HAVING COUNT(CA.cust_id) > 1;
If we had DDL and some sample data would be able to understand what you want.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 4, 2018 at 4:30 pm
I had a stab at implementing your rules, although they seem a bit contradictory:
Select
MULTI.App_id,
TableB.App_acc,
TableB.CID,
Max(IsNull(MISS.MissingAccount, 0)) As MissingAccount,
Max(IsNull(MULTI.MultiAccount, 0)) As MultiAccount
From TableB
Left Join
(
Select
TableB.CID,
Max(IIF(TableA.App_acc Is Null,1,0)) As MissingAccount
from TableB
left join TableA On TableA.App_acc = TableB.App_acc
group by
TableB.App_acc,
TableB.CID
) MISS On TableB.CID = MISS.CID
Left Join
(
Select A.App_id,App_acc,MultiAccount
From TableA A
Outer Apply
(
select
App_id,
IIF(Count(Distinct CID) = 1, 0,1) MultiAccount
from TableA join TableB on TableA.App_acc = TableB.App_acc
Where App_id = A.App_id
group by App_id
) M
) MULTI On TableB.App_acc = MULTI.App_acc
Group By MULTI.App_id,TableB.App_acc,TableB.CID
Order By MULTI.App_id,TableB.App_acc
But the results don't match your "expected" ones and I'm not sure how they could to be honest. For example the third row in your screenshot with App_Acc of 1200000000000000 has an App_ID of 1. But there is no way to logically extract that value from TableA since there isn't an associated row there (which is kind of the point). It sort of feels like you're trying to squash two reports into one that would be much better kept logically separate, or that you really need an aggregated overview keyed by App_Acc and you're trying to put more data in the results than really makes sense (which leads to issues with "gaps")
July 9, 2018 at 9:51 am
I think this should work:SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE #Account_Customer (
[Per_id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[App_acc] [nvarchar](16) NOT NULL,
[CID] [nvarchar](10) NOT NULL,
);
GO
CREATE TABLE #Application_Account (
[Apacid] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[App_id] [int] NOT NULL,
[App_acc] [nvarchar](16) NOT NULL,
);
GO
SET IDENTITY_INSERT #Account_Customer ON;
INSERT #Account_Customer ([Per_id], [App_acc], [CID])
VALUES (1, N'1000000000000000', N'0000000001'),
(2, N'2000000000000000', N'0000000002'),
(3, N'3000000000000000', N'0000000003'),
(4, N'4000000000000000', N'0000000004'),
(5, N'5000000000000000', N'0000000005'),
(6, N'6000000000000000', N'0000000006'),
(7, N'7000000000000000', N'0000000007'),
(8, N'8000000000000000', N'0000000008'),
(9, N'9000000000000000', N'0000000009'),
(10, N'1100000000000000', N'0000000001'),
(11, N'1200000000000000', N'0000000001'),
(12, N'1300000000000000', N'0000000003'),
(13, N'1400000000000000', N'0000000001'),
(14, N'1500000000000000', N'0000000010'),
(15, N'1600000000000000', N'0000000010'),
(16, N'1700000000000000', N'0000000011'),
(17, N'1800000000000000', N'0000000011');
SET IDENTITY_INSERT #Account_Customer OFF;
SET IDENTITY_INSERT #Application_Account ON;
INSERT #Application_Account ([Apacid], [App_id], [App_acc])
VALUES (1, 1, N'1000000000000000'),
(2, 1, N'1100000000000000'),
(3, 2, N'2000000000000000'),
(4, 3, N'3000000000000000'),
(5, 4, N'4000000000000000'),
(6, 4, N'5000000000000000'),
(7, 5, N'5000000000000000'),
(8, 5, N'6000000000000000'),
(9, 6, N'1000000000000000'),
(10, 7, N'1500000000000000'),
(11, 7, N'1700000000000000');
SET IDENTITY_INSERT #Application_Account OFF;
WITH AppCustomerCounts AS (
SELECT AA.App_id,
COUNT(DISTINCT AC.CID) AS CustCount
FROM #Application_Account AS AA
INNER JOIN #Account_Customer AS AC
ON AA.App_acc = AC.App_acc
GROUP BY AA.App_id
)
SELECT DISTINCT
C.CID,
C.App_acc,
APP.App_id,
CASE
WHEN APP.App_id IS NULL THEN 1
ELSE 0
END AS AppNOTExistsFlag,
CASE
WHEN APP.CustCount > 1 THEN 1
ELSE 0
END AS MultipleAccountsFlag
FROM #Account_Customer AS C
OUTER APPLY (
SELECT TOP (1) A.*, ACC.CustCount
FROM #Application_Account AS A
INNER JOIN AppCustomerCounts AS ACC
ON A.App_id = ACC.App_id
WHERE A.App_acc = C.App_acc
ORDER BY A.App_id
) AS APP
ORDER BY
C.CID,
C.App_acc;
DROP TABLE #Application_Account;
DROP TABLE #Account_Customer;
Please note that TableA and TableB have more meaningful names in this case, reflecting their actual roles.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 9, 2018 at 12:52 pm
@Everybody,
I would like to thank you all for your assistance. Being a junior in SQL, this was a bit out of my league.
I shall include my solution to this task, feel free to review and comment.
IF OBJECT_ID('tempdb..#MCID') IS NOT NULL DROP TABLE #MCID;
IF OBJECT_ID('tempdb..#CID') IS NOT NULL DROP TABLE #CID;
IF OBJECT_ID('tempdb..#NDEF') IS NOT NULL DROP TABLE #NDEF;
IF OBJECT_ID('tempdb..#BOTH') IS NOT NULL DROP TABLE #BOTH;
IF OBJECT_ID('tempdb..#NORMAL') IS NOT NULL DROP TABLE #NORMAL;
/*INSERT App_id WITH MULTIPLE CIDs*/
SELECT
APP_ID
,COUNT(DISTINCT B.CID) AS CID
INTO #MCID
FROM [dbo].[Application_Account] AS A
INNER JOIN dbo.Account_Customer AS B
ON B.App_acc = A.App_acc
GROUP BY A.APP_ID
HAVING COUNT(DISTINCT B.CID) > 1
/*INSERT MULTI CID APPS*/
SELECT DISTINCT
A.App_id
,B.App_acc
,B.CID
,CAST(0 AS BIT) NotDefinedAccount
,CAST(1 AS BIT) MultipleCIDs
INTO #CID
FROM dbo.Account_Customer AS B
INNER JOIN dbo.Application_Account AS A
ON A.App_acc = B.App_acc
WHERE A.App_id IN (SELECT DISTINCT #MCID.App_id FROM #MCID)
/*INSERT APPLICATIONS WITH NOT DEFINED ACCOUNT*/
;WITH
MISS_ACC AS (
SELECT
A.App_id
,A.App_acc
,B.CID
FROM dbo.Account_Customer AS B
INNER JOIN dbo.Application_Account AS A
ON B.App_acc = A.App_acc
)
SELECT DISTINCT
MISS_ACC.App_id
,B.App_acc
,B.CID
,CAST(1 AS BIT) NotDefinedAccount
,CAST(0 AS BIT) MultipleCIDs
INTO #NDEF
FROM dbo.Account_Customer AS B
INNER JOIN MISS_ACC
ON MISS_ACC.CID = B.CID
WHERE NOT EXISTS
(
SELECT 1
FROM MISS_ACC, dbo.Application_Account AS A
WHERE MISS_ACC.App_acc = B.App_acc
AND MISS_ACC.App_id = A.App_id
)
/*INSERT NORMAL ACCOUNTS*/
SELECT DISTINCT
A.App_id
,B.App_acc
,B.CID
,CAST(0 AS BIT) NotDefinedAccount
,CAST(0 AS BIT) MultipleCIDs
INTO #NORMAL
FROM dbo.Application_Account AS A
INNER JOIN dbo.Account_Customer AS B
ON B.App_acc = A.App_acc
/*FIND APPLICATIONS WITH BOTH FLAGS*/
SELECT DISTINCT
#NDEF.App_id
,#NDEF.App_acc
,#NDEF.CID
,CAST(1 AS BIT) NotDefinedAccount
,CAST(1 AS BIT) MultipleCIDs
INTO #BOTH
FROM #NDEF
INNER JOIN #CID
ON #CID.App_id = #NDEF.App_id
/*FINAL QUERY*/
SELECT DISTINCT
#CID.App_id
,#CID.App_acc
,#CID.CID
,#CID.NotDefinedAccount
,#CID.MultipleCIDs
FROM #CID
UNION
SELECT DISTINCT
#NDEF.App_id
,#NDEF.App_acc
,#NDEF.CID
,#NDEF.NotDefinedAccount
,#NDEF.MultipleCIDs
FROM #NDEF
UNION
SELECT DISTINCT
#NORMAL.App_id
,#NORMAL.App_acc
,#NORMAL.CID
,#NORMAL.NotDefinedAccount
,#NORMAL.MultipleCIDs
FROM #NORMAL
WHERE #NORMAL.App_id IN (SELECT #NDEF.App_id FROM #NDEF)
AND #NORMAL.App_id NOT IN (SELECT #CID.App_id FROM #CID)
UNION
SELECT DISTINCT
#BOTH.App_id
,#BOTH.App_acc
,#BOTH.CID
,#BOTH.NotDefinedAccount
,#BOTH.MultipleCIDs
FROM #BOTH
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply