sql query

Hi Guys,

 Need help with SQL query! I have 2 tables customer and account, now i need to get customer and then all its accounts and then 2nd customer with all its account e.g

    customer 111 has  3 accounts 1 , 2 , 3 and customer 222  has accounts 11 , 22 , 33  so it should show like this in the result

  customer 111
  1 account
  2 account
  3 account
 customer 222
  11 account
  22 account
  33 account
Syed Ali Shah RashdiT24/Insight Technical ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
Try something like this:

select c.customer, a.account
from customer_table c
inner join account_table a
on c.customer = a.customer
order by c.customer, a.account;

Open in new window


»bp
0
Syed Ali Shah RashdiT24/Insight Technical ConsultantAuthor Commented:
Hi bill row 1 will have a customer and then account in row 2 , 3 ,4 so on ... then same for the new customer.
0
Bill PrewIT / Software Engineering ConsultantCommented:
So you want one column output, but that column will contain either the customer, or the account?  That seems a bit confusing.  Typically I might expect there to be two columns, one for customer, one for account, and only show the customer on the first account line, like:

CUSTOMER      ACCOUNT
------------- --------------
customer 111  1 account
              2 account
              3 account
customer 222  11 account
              22 account
              33 account

Open in new window

Either one of these takes a little bit of complexity in the SQL, and it can be different depending on the database platform involved.  What are you working with (SQL Server, Oracle, MySQL, etc)?


»bp
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Syed Ali Shah RashdiT24/Insight Technical ConsultantAuthor Commented:
hi bill this should look like this .

CUSTOMER      ACCOUNT
------------- --------------
customer 111
 1 account
 2 account
 3 account
customer 222
 11 account
 22 account
 33 account

using sql server
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, give this a try, here is my test tables, data and query, followed by the generated results.

CREATE TABLE customer_table (
    customer_id  INTEGER,
    customer_name VARCHAR(30)
);

CREATE TABLE account_table (
    account_id  INTEGER,
    customer_id  INTEGER,
    account_name VARCHAR(30)
);


INSERT INTO customer_table
    (customer_id, customer_name)
VALUES
    (1, 'Customer One'),
    (2, 'Customer Two');

INSERT INTO account_table
    (account_id, customer_id, account_name)
VALUES
    (1, 1, 'Account One'),
    (2, 1, 'Account Two'),
    (3, 1, 'Account Three'),
    (4, 2, 'Account Four'),
    (5, 2, 'Account Five'),
    (6, 2, 'Account Six'),
    (7, 2, 'Account Seven');

SELECT c.customer_name, a.account_name
FROM customer_table c
INNER JOIN account_table a
ON c.customer_id = a.customer_id
ORDER BY c.customer_name, a.account_name;

SELECT CASE WHEN RN = 1 THEN x.customer_name ELSE '' END CUSTOMER,
       x.account_name AS ACCOUNT
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY c.customer_name ORDER BY c.customer_name) RN,
             c.customer_name,
             a.account_name
      FROM customer_table c
      INNER JOIN account_table a
          ON c.customer_id = a.customer_id) x;

Open in new window

sshot-435.png

»bp
0
Syed Ali Shah RashdiT24/Insight Technical ConsultantAuthor Commented:
i want to create txt file with first line cutomer then all its account then 2nd customer then all it account !
0
Bill PrewIT / Software Engineering ConsultantCommented:
So perhaps something like this:

SELECT CASE WHEN RN = 1 THEN 'CUSTOMER: ' + x.customer_name ELSE ' ACCOUNT: ' + x.account_name END COLUMN1
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY c.customer_name ORDER BY c.customer_name) RN,
             c.customer_name,
             a.account_name
      FROM customer_table c
      INNER JOIN account_table a
          ON c.customer_id = a.customer_id) x;

Open in new window


»bp
0
Syed Ali Shah RashdiT24/Insight Technical ConsultantAuthor Commented:
ok this kinda works ! how do i add more columns in case ? there are like 7 or 8 calumns for customer and account ?
0
Bill PrewIT / Software Engineering ConsultantCommented:
How do you want the output to look?


»bp
0
Syed Ali Shah RashdiT24/Insight Technical ConsultantAuthor Commented:
its when i put more where clause it just removes the account and shows customer only ... these where clauses are on account and i can see the data in the table .
0
Bill PrewIT / Software Engineering ConsultantCommented:
Please post the query that is not working.


»bp
0
Syed Ali Shah RashdiT24/Insight Technical ConsultantAuthor Commented:
here is the query

SELECT CASE WHEN RN = 1 THEN x.[Record Type]  ELSE x.[Record Type2] END COLUMN1

, case when RN = 1 THEN x.[Account Holder Type]   ELSE x.[A/C Opening Date ] END COLUMN2

, case when RN = 1 THEN x.[Member’s Unique Customer id No. ]   ELSE x.[Filing Code ] END COLUMN3

, case when RN = 1 THEN x.Name   ELSE x.[A/C Reference Number ] END COLUMN4

--, case when RN = 1 THEN x.[Address Line 1 ]   ELSE x.Amount END COLUMN5

, case when RN = 1 THEN x.[Address Line 2 ]   ELSE x.[Currency Indicator ] END COLUMN6

, case when RN = 1 THEN x.[Address Line 3 ]   ELSE x.Term END COLUMN7

, case when RN = 1 THEN x.[Gender code ]   ELSE x.[Association with Account ] END COLUMN8

, case when RN = 1 THEN x.Occupation   ELSE x.[Loan Type ] END COLUMN9

, case when RN = 1 THEN x.[Reserved ]   ELSE x.[Payment Frequency ] END COLUMN10

, case when RN = 1 THEN x.[Junior/Senior Indicator ]   ELSE x.[Bank Sort Code ] END COLUMN11

, case when RN = 1 THEN x.[Home Telephone No. ]  ELSE x.[Bank A/C Reference ] END COLUMN12

, case when RN = 1 THEN x.[Work Telephone No.]   ELSE x.[Return Code 2 ] END COLUMN13

, case when RN = 1 THEN x.[Mobile Telephone No. ]   ELSE x.Filler  END COLUMN14

, case when RN = 1 THEN x.[Return Code ]  ELSE x.[End of Record Indicator1 ] END COLUMN15

, case when RN = 1 THEN x.[Date of Birth]   ELSE ''   END COLUMN16

, case when RN = 1 THEN x.[End of Record Indicator ]   ELSE '' END COLUMN17

, case when RN = 1 THEN x.Category  ELSE x.Category END category

, case when RN = 1 THEN x.IsNewToday  ELSE x.IsNewToday END isnewtoday

 

FROM (SELECT ROW_NUMBER() OVER (PARTITION BY cu.CustomerNum ORDER BY cu.CustomerNum) RN,

       CAST('X1' AS CHAR(2)) AS 'Record Type'

       ,CAST(cu.CustomerSector AS CHAR(1)) AS 'Account Holder Type'

          ,CAST(cu.CustomerNum AS CHAR(20)) AS 'Member’s Unique Customer id No. '

          ,CAST(COALESCE(cu.[FirstName],'')+','+ COALESCE(cu.[LastName],'') AS CHAR(35)) as 'Name'

          ,COALESCE(CAST(cu.[CustAddressLine1] AS CHAR(30)),'') as 'Address Line 1 '

      ,COALESCE(CAST(cu.[CustAddressLine2] AS CHAR(25)),'') as 'Address Line 2 '

      ,COALESCE(CAST(cu.[CustAddressLine3] AS CHAR(20)),'') as 'Address Line 3 '

         ,COALESCE(CAST(cu.gender AS CHAR(1)),'') as 'Gender code '

         ,CAST(CU.[Occupation] AS CHAR(25)) AS 'Occupation'

         ,CAST('' AS CHAR(19)) AS 'Reserved '

         ,convert(varchar(8),cu.BirthDate,112) as 'Date of Birth'

         ,CAST('S' AS CHAR(1)) AS'Junior/Senior Indicator '

         ,CAST(CU.DefaultPhone AS CHAR(20)) AS'Home Telephone No. '

         ,CAST(CU.WorkPhone AS CHAR(20)) AS'Work Telephone No.'

         ,CAST(CU.MobilePhone AS CHAR(20)) AS'Mobile Telephone No. '

         ,CAST(' ' AS CHAR(2)) AS 'Return Code 2 '

         ,CAST('.' AS CHAR(1)) AS 'End of Record Indicator1 '

      

         ,CAST('X2' AS CHAR(2)) AS 'Record Type2'

         ,CAST(ac.OriginalStartDate AS CHAR(8)) AS 'A/C Opening Date '

         ,CAST('' AS CHAR(6)) AS 'Filing Code '

         ,CAST(ac.AccountNum AS CHAR(20)) AS 'A/C Reference Number '

         ,CAST(ac.OriginalLoanAmount AS char(10)) AS 'Amount'

         ,CAST('EUR' AS CHAR(3)) AS 'Currency Indicator '

         ,CAST(ac.TermInMonths AS CHAR(3)) AS 'Term'

         ,CAST('' AS CHAR(1)) AS 'Association with Account '

         ,CAST('PL' AS CHAR(3)) AS 'Loan Type '

         ,CAST(ac.PmtFreq AS CHAR(1)) AS 'Payment Frequency '

         ,CAST('' AS CHAR(6)) AS 'Bank Sort Code '

         ,CAST('' AS CHAR(8)) AS 'Bank A/C Reference '

         ,CAST('' AS CHAR(2)) AS 'Return Code '

         ,CAST('' AS CHAR(182)) AS 'Filler'

         ,CAST('.' AS CHAR(1)) AS 'End of Record Indicator '

         ,ac.Category , ac.IsNewToday

       FROM [InsightWarehouse].[dbo].[v_Customer] cu

INNER JOIN [InsightWarehouse].[dbo].[v_Account] ac

ON  ac.CustomerId = cu.CustomerId

where cu.BusinessDate = (select max(BusinessDate) from InsightMasterData.dbo.CurrentDate) ) x;

Open in new window



works well until i put
and ac.category = 'loan' and ac.IsNewToday ='yes'

Open in new window

 i can see account with that clause in the table but this query doesnt get it back just only customer .
0
Syed Ali Shah RashdiT24/Insight Technical ConsultantAuthor Commented:
also i want it the output if there is multiple account for this custoemr then it should show like

customer 1
account 1
customer 1
account 2
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, I was thinking about this a bit more, and I think we are not in a good place.

My comment in #a42471686 was accurate.  It showed all the data accurately, just not in the format you wanted.

In trying to get to the format you wanted I proposed #a42471833 which you built off of.  However it occurred to me as I was looking at your larger query, that there is a problem.

The basic idea is that we are building a join query first that produces results from the join where we have all the columns from the customer table, followed by all the columns in the accounts table, on each result row.  So that means we have a results set of:

customer1 + account1(forcustomer1)
customer1 + account2(forcustomer1)
customer1 + account3(forcustomer1)
customer2 + account1(forcustomer2)
customer2 + account2(forcustomer2)
customer2 + account3(forcustomer2)

But the query we are now working with is either selecting the customer info only (when it is the first for of a new customer), or the account info only (when it is not the first row for a customer).  As a result, we are selecting the following from the results set:

customer1
account2(forcustomer1)
account3(forcustomer1)
customer2
account2(forcustomer2)
account3(forcustomer2)

Notice that we are losing the first account for each customer, which clearly is not useful.

I'm not sure how to provide what you are looking for from a SQL query at the moment, it may be possible, it's just not something I have seen done.  What you are describing is more like "header" and "detail" logic of a reporting tool.


»bp
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, I poked around at this a bit further and I think this might be an approach.  I think with a little work you can adapt it to your data, you may need to convert some columns to get the UNION to work properly, my test data kept it simple.  The query is below, but take a look at this link to see my tables, data, and test results, I think it aligns with the concept you are after.


CREATE TABLE customer_table (
    customer_id  INTEGER,
    customer_name VARCHAR(30),
    customer_data1 VARCHAR(30),
    customer_data2 VARCHAR(30)
);

CREATE TABLE account_table (
    account_id  INTEGER,
    customer_id  INTEGER,
    account_name VARCHAR(30),
    account_data1 VARCHAR(30),
    account_data2 VARCHAR(30)
);


INSERT INTO customer_table
    (customer_id, customer_name, customer_data1, customer_data2)
VALUES
    (1, 'Customer One', 'Customer Data 1A', 'Customer Data 1B'),
    (2, 'Customer Two', 'Customer Data 2A', 'Customer Data 2B');

INSERT INTO account_table
    (account_id, customer_id, account_name, account_data1, account_data2)
VALUES
    (1, 1, 'Account One',   'Account Data 1A', 'Account Data 1B'),
    (2, 1, 'Account Two',   'Account Data 2A', 'Account Data 2B'),
    (3, 1, 'Account Three', 'Account Data 3A', 'Account Data 3B'),
    (4, 2, 'Account Four',  'Account Data 4A', 'Account Data 4B'),
    (5, 2, 'Account Five',  'Account Data 5A', 'Account Data 5B'),
    (6, 2, 'Account Six',   'Account Data 6A', 'Account Data 6B'),
    (7, 2, 'Account Seven', 'Account Data 7A', 'Account Data 7B');

SELECT * FROM customer_table;

SELECT * FROM account_table;

SELECT C1, C2, C3, C4
FROM
(
    SELECT 'C' AS C1,
           c.customer_name AS C2,
           c.customer_data1 AS C3,
           c.customer_data2 AS C4,
           CONVERT(FLOAT,c.customer_id) AS seq
    FROM customer_table c

    UNION ALL

    SELECT 'A' AS C1,
           a2.account_name AS C2,
           a2.account_data1 AS C3,
           a2.account_data2 AS C4,
           a2.seq AS seq
           FROM 
           (
           SELECT a.*,
           CONCAT(c.customer_id, '.', ROW_NUMBER() OVER (PARTITION BY c.customer_id ORDER BY c.customer_id, a.account_id)) AS seq
           FROM customer_table c
           INNER JOIN account_table a
           ON c.customer_id = a.customer_id
           ) a2

) u
ORDER BY seq;

Open in new window


sshot-441.png

»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.