sql query

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;

Select allOpen in new window

---

»bp

Hi bill row 1 will have a customer and then account in row 2 , 3 ,4 so on ... then same for the new customer.

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

Select allOpen 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

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

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;

Select allOpen in new window

---

»bp

i want to create txt file with first line cutomer then all its account then 2nd customer then all it account !

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;

Select allOpen in new window

---

»bp

ok this kinda works ! how do i add more columns in case ? there are like 7 or 8 calumns for customer and account ?

How do you want the output to look?

---

»bp

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 .

Please post the query that is not working.

---

»bp

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;

Select allOpen in new window

works well until i put

and ac.category = 'loan' and ac.IsNewToday ='yes'

Select allOpen in new window

 i can see account with that clause in the table but this query doesnt get it back just only customer .

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

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:


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:


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