Extracting records from two checks table

Transact-SQL
1

Good day to all. I have three tables where I have to extract information on the customer (first table), checks issued to customer (2nd table), and supplementary checks issued (3rd table). Below is the sample of my query command:

SELECT a.CLAIM_NO, Name, b.d_issue, b.amount, b.check_no, c.d_issue, c.amount, c.check_no, c.reason
from customer_info a left join (select claim_no, d_issue, amount, check_no from checks_table
where d_issue between '01/01/1990' and '12/31/1994') b on a.claim_no = b.claim_no
left join (select claim_no, d_issue, check_no, amount, reason from supplementary_checks
where d_printed between '01/01/1990' and '12/31/1994') c on a.claim_no = c.claim_no

The query produces all checks issued and supplementary checks to the customer. However, in the Supplementary_Checks, if there is only 1 check for the customer, the info displayed are multiple redundant entries the same number as the number of info for the Checks_Table.

Below is my desired output:
Name b.D_Issue b.Amount b.Check_no c.D_issue c.Amount c.Check_no c.Reason
John Snow 01/01/2015 500.00 11111 02/10/2015 1000.00 12123 Payment for...
02/01/2015 500.00 22222
03/01/2015 500.00 33333
04/01/2015 500.00 44444

Hope somebody can provide me a solution because I am stuck in my query for a couple days now. Thanks in advance to all.

2

Hi

I am trying to understand your problem

I am pasting my script to duplicate your problem

Please tell me if this is accurate

/*
DROP TABLE #customer

CREATE TABLE #customer (
NAME VARCHAR(100) NULL
,claimNo INT NULL
,
)

INSERT INTO #customer
SELECT 'harish'
,123456

DROP TABLE #checksCustomer

CREATE TABLE #checksCustomer (
claimNo INT NULL
,
-- d_issue datetime null,
amount INT NULL
,check_no INT NULL
)

INSERT INTO #checksCustomer
SELECT 123456
,100
,11111

INSERT INTO #checksCustomer
SELECT 123456
,100
,567567

DROP TABLE #suppChecks

CREATE TABLE #suppChecks (
claimNo INT NULL
,
--d_issue datetime null,
amount INT NULL
,check_no INT NULL
,reason VARCHAR(100) NULL
)

INSERT INTO #suppChecks
SELECT 123456
,200
,222222
,'Needed'

*/

SELECT a.claimNo
,NAME
,b.amount
,b.check_no
,c.amount
,c.check_no
,c.reason
FROM #customer a
LEFT JOIN (
SELECT claimNo
,amount
,check_no
FROM #checksCustomer
) b ON a.claimNo = b.claimNo
LEFT JOIN (
SELECT claimNo
,check_no
,amount
,reason
FROM #suppChecks
) c ON a.claimNo = c.claimNo

3

Hi i pasted earlier
the query result looks like this

claimNo NAME amount check_no amount check_no reason
123456 harish 100 11111 200 222222 Needed
123456 harish 100 567567 200 222222 Needed

is this an accurate description of your problem ?

4

i am pasting the query result again .. in a better way
is this accurate description of ur problem

Claim CustName custCheckNo custCheckAmount suppCheckNo suppCheckAmt suppCheckReason
123456 harish 11111 100 222222 200 Needed
123456 harish 567567 100 222222 200 Needed

5

hi HHRR! thank your for your reply. Your query is the exact result i'm getting. however, my dilemma is in the suppchecks where if i have only 1 check, it will only display the one result and will not repeat the same details to match the number of records in checks table.

6

Hi

As long as you have the "left join to the customer checks table" it will always do that ..

You have two options depending on your requirements

  1. Remove the left join to the CustomerChecks table
    see i commented it out below
    SELECT a.claimNo as Claim
    ,NAME as CustName
    --,b.check_no as custCheckNo
    --,b.amount as custCheckAmount
    ,c.check_no as suppCheckNo
    ,c.amount as suppCheckAmt
    ,c.reason as suppCheckReason
    FROM #customer a
    -- LEFT JOIN (
    -- SELECT claimNo
    -- ,amount
    -- ,check_no
    -- FROM #checksCustomer
    -- ) b ON a.claimNo = b.claimNo
    LEFT JOIN (
    SELECT claimNo
    ,check_no
    ,amount
    ,reason
    FROM #suppChecks
    ) c ON a.claimNo = c.claimNo

Result Set
Claim CustName suppCheckNo suppCheckAmt suppCheckReason
123456 harish 222222 200 Needed

  1. Out of the records for the select the MAX(custCheckNo) .. or MIN()

SELECT a.claimNo as Claim
,NAME as CustName
,max(b.check_no) as custCheckNo
,b.amount as custCheckAmount
,c.check_no as suppCheckNo
,c.amount as suppCheckAmt
,c.reason as suppCheckReason
FROM #customer a
LEFT JOIN (
SELECT claimNo
,amount
,check_no
FROM #checksCustomer
) b ON a.claimNo = b.claimNo
LEFT JOIN (
SELECT claimNo
,check_no
,amount
,reason
FROM #suppChecks
) c ON a.claimNo = c.claimNo
Group by
a.claimNo
,NAME
,b.amount
,c.check_no
,c.amount
,c.reason

Result Set
Claim CustName custCheckNo custCheckAmount suppCheckNo suppCheckAmt suppCheckReason
123456 harish 567567 100 222222 200 Needed

7

Hi I posted above .. I am posting the same thing again with nice formatting

As long as you have the "left join to the customer checks table" it will always do that ..
You have two options depending on your requirements

  1. Remove the left join to the CustomerChecks table
    see i commented it out below

    SELECT a.claimNo AS Claim
    ,NAME AS CustName
    --,b.check_no as custCheckNo
    --,b.amount as custCheckAmount
    ,c.check_no AS suppCheckNo
    ,c.amount AS suppCheckAmt
    ,c.reason AS suppCheckReason
    FROM #customer a
    -- LEFT JOIN (
    -- SELECT claimNo
    -- ,amount
    -- ,check_no
    -- FROM #checksCustomer
    -- ) b ON a.claimNo = b.claimNo
    LEFT JOIN (
    SELECT claimNo
    ,check_no
    ,amount
    ,reason
    FROM #suppChecks
    ) c ON a.claimNo = c.claimNo

  1. Out of the records for the select the MAX(custCheckNo) .. or MIN()

    SELECT a.claimNo AS Claim
    ,NAME AS CustName
    ,max(b.check_no) AS custCheckNo
    ,b.amount AS custCheckAmount
    ,c.check_no AS suppCheckNo
    ,c.amount AS suppCheckAmt
    ,c.reason AS suppCheckReason
    FROM #customer a
    LEFT JOIN (
    SELECT claimNo
    ,amount
    ,check_no
    FROM #checksCustomer
    ) b ON a.claimNo = b.claimNo
    LEFT JOIN (
    SELECT claimNo
    ,check_no
    ,amount
    ,reason
    FROM #suppChecks
    ) c ON a.claimNo = c.claimNo
    GROUP BY a.claimNo
    ,NAME
    ,b.amount
    ,c.check_no
    ,c.amount
    ,c.reason

    Result - 2 .jpg889×102 35.3 KB

/*
DROP TABLE #customer

CREATE TABLE #customer (
NAME VARCHAR(100) NULL
,claimNo INT NULL
,
)

INSERT INTO #customer
SELECT 'harish'
,123456

DROP TABLE #checksCustomer

CREATE TABLE #checksCustomer (
claimNo INT NULL
,
-- d_issue datetime null,
amount INT NULL
,check_no INT NULL
)

INSERT INTO #checksCustomer
SELECT 123456
,100
,11111

INSERT INTO #checksCustomer
SELECT 123456
,100
,567567

DROP TABLE #suppChecks

CREATE TABLE #suppChecks (
claimNo INT NULL
,
--d_issue datetime null,
amount INT NULL
,check_no INT NULL
,reason VARCHAR(100) NULL
)

INSERT INTO #suppChecks
SELECT 123456
,200
,222222
,'Needed'

*/

8

You can also re-edit your earlier post(s) using the Pencil Icon if you need to make a change :slight_smile:

1 Like
9

Thanks HHRR for the reply. however, i'm still getting the same result in the suppchecks table. The record in the checkscustomer table will duplicate a number of times with the same number of record found in the suppchecks table. I would want the results be previewed same as below:

ClaimNo CustName CheckNO ChAmt -SuppChNo SuppChAmt
123456---J. Hall ---- 123456 -- 500.00--987654 --- 1000.00
123456---J. Hall ---- 234567 -- 800.00--876543 ---- 500.00
123456---J. Hall ---- 345678 -- 500.00
123456---J. Hall ---- 456789 -- 500.00