locked
LINQ/SQL Report Query that has subsection RRS feed

  • Question

  • User-1244692504 posted

    I have been battling with this query and want expert advice if my wishing could work

    I have two tables 

    Underwriting_Client
    Underwriting_ClientContact

    Underwriting_Client stores the client KYC details (Name, Address, PhoneNo) 
    While Underwriting_ClientContact stores the Client’s contact details which is at least one person. It can be more than that.

    The two tables were related together on ClientCode.

    The question is how do I relate the two tables together in such a way that I will have a subsection under each client’s listing the contact persons.
    
    For example:
    
    BB & Co Limited 	                        22, Kalahari Street, Ajaokuta   08095897257
    	Laurenso Ajay				25, Akinkemi Street, RingRd	08882972992
    	Lukeman Oduoye				45, Are Avenue, Oluyole	        08092342345
    	Badmus Stone				32, Kongi, Bodija Ibadan	08062346474
    
    MaiMain Cable Ltd.			        Victoria Island		        09076345151
    	Bombuck Aderemi				22, Palmgroove, Lagos	        08072345262
    	Jolyjo Fakorede			        33, Surukere, Lagos	        08192536352
    
    Etc.
    
    Is this doable using Sql query or LINQ ?
    
    An example will be very much appreciated.
    
    

    Below are the fields for each table

    
    Underwriting_Client
            [ClientCode] [char](10) NOT NULL,
    	[Title] [varchar](25) NULL,
    	[Name] [varchar](50) NOT NULL,
    	[Occupation] [varchar](50) NULL,
    	[DateOpen] [date] NULL,
    	[BranchCode] [char](8) NULL,
    	[Address] [varchar](200) NULL,
    	[State] [varchar](15) NULL,
    	[Country] [varchar](40) NULL,
    	[Telephone] [varchar](50) NULL,
    	[Email] [varchar](50) NULL,
    
    
    Underwriting_ClientContact
            [ClientCode] [char](10) NULL,
    	[ContactTitle] [varchar](25) NULL,
    	[ContactFullName] [varchar](50) NULL,
    	[ContactDesignation] [varchar](50) NULL,
    	[ContactPhoneNumber] [varchar](50) NULL,
    	[ContactEmailAddress] [varchar](50) NULL,
    	[RecordID] [int] IDENTITY(1,1) NOT NULL,
    	[ModifiedBy] [varchar](32) NOT NULL,
    	[RowVersionNo] [timestamp] NOT NULL
    

    Below is the query that I have tried, although not working as desire.

    SELECT
      a.ClientCode, a.Name AS ClientName, a.Title As ClientTittle, a.Address As ClientAddress, a.BranchCode 
      FROM 
      Underwriting_Client a
    
    WHERE
        EXISTS (
            SELECT
                ClientCode, ContactTitle, ContactFullName, ContactDesignation, ContactPhoneNumber, ContactEmailAddress
            FROM
                Underwriting_ClientContact o
            WHERE
                o.ClientCode = a.ClientCode
        )
    ORDER BY
     BranchCode, 
     ClientName

    Thank you in anticipation of your kind assistance

    Best regards

    Lawrence

    Monday, December 7, 2020 5:29 PM

Answers

  • User-1244692504 posted

    Thank you so much YihuiSun for your preparedness to help others despite your tight schedule. This is greatly appreciated. I tried your code, it worked but combined the fields on the same row. I, therefore, research further to get the contacts arranged under each Client as display in my example. 

    The query that achieved it is pasted below for others who might be having the same challenge.

    SELECT * FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY 
    BranchCode) AS NUMBER, *  
    From (
    SELECT	ClientCode, Title AS ClientTittle, Name AS ClientName,  Address AS ClientAddress, Telephone AS ClientTelephone, Email AS ClientEmail, BranchCode, -1 CompanyOrder, ClientType As ClientCategory, ClientStatus, Town AS ClientTown, State As ClientState, Country As ClientCountry  FROM Underwriting_Client
    WHERE BranchCode='00000001' AND  ClientStatus ='001'
    UNION
    SELECT b.ClientCode, b.Contacttitle AS ClientTittle, '     '+ b.Contactfullname AS ClientName, '     '+ b.Contactdesignation AS ClientAddress, b.Contactphonenumber AS ClientTelephone, b.ContactEmailAddress AS ClientEmail, c.BranchCode, 0 CompanyOrder, c.ClientType AS ClientCategory, c.ClientStatus, c.Town AS ClientTown, c.State As ClientState, c.Country AS ClientCountry  FROM Underwriting_ClientContact b
    INNER JOIN Underwriting_client AS c ON c.ClientCode =b.ClientCode
    WHERE BranchCode='00001' AND  ClientStatus ='001' AND ClientType ='001' OR ClientType='009') AS TBLALL 
     ) AS TBL 
    
    ORDER BY BranchCode, ClientCategory, ClientCode, CompanyOrder, ClientName ASC 

    What does the trick are the  UNION-1 CompanyOrder, and  0 CompanyOrder.  The -1 will list the Client while the 0 will list the contact under each client.

    All the WHERE clauses can be removed if you don't want to filer off any Client. The result will be all clients both Active and Dormant.

    Once again, Thank you so much for your heart of Gold.

    Best regards,

    Lawrence

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 18, 2020 9:58 AM

All replies

  • User1686398519 posted

    Hi Lawrence_Ajayi, 

    You can use SQL JOIN to achieve your needs.

    • SQL JOIN: A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
    SELECT a.ClientCode, a.Name AS ClientName, a.Title As ClientTittle, a.Address As ClientAddress, a.BranchCode, 
    o.ContactTitle, o.ContactFullName, o.ContactDesignation, o.ContactPhoneNumber, o.ContactEmailAddress
    FROM  Underwriting_Client a
    INNER JOIN Underwriting_ClientContact o ON o.ClientCode = a.ClientCode
    ORDER BY
     a.BranchCode, 
     a.Name

    Here is the result. 

    Best Regards,

    YihuiSun

    Tuesday, December 8, 2020 3:15 AM
  • User-1244692504 posted

    Thank you so much YihuiSun for your preparedness to help others despite your tight schedule. This is greatly appreciated. I tried your code, it worked but combined the fields on the same row. I, therefore, research further to get the contacts arranged under each Client as display in my example. 

    The query that achieved it is pasted below for others who might be having the same challenge.

    SELECT * FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY 
    BranchCode) AS NUMBER, *  
    From (
    SELECT	ClientCode, Title AS ClientTittle, Name AS ClientName,  Address AS ClientAddress, Telephone AS ClientTelephone, Email AS ClientEmail, BranchCode, -1 CompanyOrder, ClientType As ClientCategory, ClientStatus, Town AS ClientTown, State As ClientState, Country As ClientCountry  FROM Underwriting_Client
    WHERE BranchCode='00000001' AND  ClientStatus ='001'
    UNION
    SELECT b.ClientCode, b.Contacttitle AS ClientTittle, '     '+ b.Contactfullname AS ClientName, '     '+ b.Contactdesignation AS ClientAddress, b.Contactphonenumber AS ClientTelephone, b.ContactEmailAddress AS ClientEmail, c.BranchCode, 0 CompanyOrder, c.ClientType AS ClientCategory, c.ClientStatus, c.Town AS ClientTown, c.State As ClientState, c.Country AS ClientCountry  FROM Underwriting_ClientContact b
    INNER JOIN Underwriting_client AS c ON c.ClientCode =b.ClientCode
    WHERE BranchCode='00001' AND  ClientStatus ='001' AND ClientType ='001' OR ClientType='009') AS TBLALL 
     ) AS TBL 
    
    ORDER BY BranchCode, ClientCategory, ClientCode, CompanyOrder, ClientName ASC 

    What does the trick are the  UNION-1 CompanyOrder, and  0 CompanyOrder.  The -1 will list the Client while the 0 will list the contact under each client.

    All the WHERE clauses can be removed if you don't want to filer off any Client. The result will be all clients both Active and Dormant.

    Once again, Thank you so much for your heart of Gold.

    Best regards,

    Lawrence

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 18, 2020 9:58 AM