SQL Join Statement

Question

SQL Join Statement

kbnyny

Ten Centuries

Points: 1038
More actions
September 2, 2010 at 11:21 am

#141708

I'm trying to join 2 or more tables together and I'm getting more record than I should. When I just join two table everything is fine
ie: SELECT CN20100.CUSTNMBR, CN20100.CPRCSTNM, CN20100.RMDTYPAL, CN20100.DOCNUMBR, CN20100.NOTEINDX, CN00300.TXTFIELD,
CN20100.ActionAmount, CN20100.CURTRXAM
FROM CN20100 INNER JOIN
CN00300 ON CN20100.NOTEINDX = CN00300.NOTEINDX
when I add another table to join I'm getting more record then I should. Here is the the second statement with the additional join.
SELECT CN20100.CUSTNMBR, CN20100.CPRCSTNM, CN20100.RMDTYPAL, CN20100.DOCNUMBR, CN20100.NOTEINDX, CN00300.TXTFIELD,
CN20100.ActionAmount, CN20100.CURTRXAM
FROM CN20100 INNER JOIN
CN00300 ON CN20100.NOTEINDX = CN00300.NOTEINDX INNER JOIN
RM20101 ON CN20100.CUSTNMBR = RM20101.CUSTNMBR
Can someone tell me what I'm doing wrong.
Thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

I don't think you're doing anything wrong. However, it sounds like the last table has a 1>Many relationship, and there are multiple records matching for the join condition.

You either need to filter that out in the where or on clause, or utilize the distinct operator.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

kbnyny Ten Centuries Points: 1038 More actions · Answer 2

Ok I have modified the SQL Statement and when I run this statement I am getting 682 record which is correct.

SELECT RM20101.DOCDATE AS bldat, RM20101.POSTDATE, RM20101.DOCNUMBR, RM00101.CUSTNAME, RM20101.CURNCYID, RM20101.ORTRXAMT,

CASE WHEN RM20101.ORTRXAMT = RM20101.CURTRXAM THEN 0 ELSE RM20101.CURTRXAM END AS 'Remaining Amt', RM20101.ORTRXAMT,

RM20101.CUSTNMBR, SOP10106.USRTAB09 AS xref1

FROM RM20101 INNER JOIN

SOP10106 ON RM20101.TRXDSCRN = SOP10106.SOPNUMBE INNER JOIN

RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR

WHERE (RM20101.CURTRXAM > '0.00') AND (RM20101.DOCDATE > '9/30/2009') AND (RM20101.ORTRXAMT >= RM20101.CURTRXAM)

when I add 2 more tables and run this statement I'm getting 332 records which is incorrect.

SELECT RM20101.DOCDATE AS bldat, RM20101.POSTDATE, RM20101.DOCNUMBR, RM00101.CUSTNAME, RM20101.CURNCYID, RM20101.ORTRXAMT,

CASE WHEN RM20101.ORTRXAMT = RM20101.CURTRXAM THEN 0 ELSE RM20101.CURTRXAM END AS 'Remaining Amt', RM20101.ORTRXAMT,

RM20101.CUSTNMBR, SOP10106.USRTAB09 AS xref1, CN20100.NOTEINDX, CN00300.TXTFIELD

FROM RM20101 INNER JOIN

SOP10106 ON RM20101.TRXDSCRN = SOP10106.SOPNUMBE INNER JOIN

RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR INNER JOIN

CN20100 ON RM20101.CUSTNMBR = CN20100.CUSTNMBR AND RM20101.DOCNUMBR = CN20100.DOCNUMBR INNER JOIN

CN00300 ON CN20100.NOTEINDX = CN00300.NOTEINDX

WHERE (RM20101.CURTRXAM > '0.00') AND (RM20101.DOCDATE > '9/30/2009') AND (RM20101.ORTRXAMT >= RM20101.CURTRXAM)

Can someone please tell me what I'm doing wrong.

Thank you.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Well, the inner joins are limiting the results. There are custnmbr/docnumber combos existing in RM20101 that aren't in CN20100, and noteindx in RM20101 that don't exist in CN00300.

My first recommendation would be to use LEFT JOIN on those two tables (CN20100 and CN00300), and then you'll simply see NULL's in the fields they should have fed.

If you need to identify the missing data, I'd start with select distinct lists in RM20101 and CN20100 on Custnmbr and docnumber, and CN20100 and CN00300 on noteindx and then compare the two to find missing items.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

kbnyny Ten Centuries Points: 1038 More actions · Answer 4

Craig, I'm not sure I understand what you are saying when you say if I do the left join on the two tables, I will see a Null's on the fields that has record. That is exactly what I want to to show. Any recored that does not have value in to show Null.

SELECT RM20101.DOCDATE AS bldat, RM20101.POSTDATE, RM20101.DOCNUMBR, RM00101.CUSTNAME, RM20101.CURNCYID, RM20101.ORTRXAMT,

CASE WHEN RM20101.ORTRXAMT = RM20101.CURTRXAM THEN 0 ELSE RM20101.CURTRXAM END AS 'Remaining Amt', RM20101.ORTRXAMT,

RM20101.CUSTNMBR, SOP10106.USRTAB09 AS xref1, CN20100.NOTEINDX, CN00300.TXTFIELD

FROM RM20101 INNER JOIN

SOP10106 ON RM20101.TRXDSCRN = SOP10106.SOPNUMBE INNER JOIN

RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR INNER JOIN

CN20100 ON RM20101.CUSTNMBR = CN20100.CUSTNMBR AND RM20101.DOCNUMBR = CN20100.DOCNUMBR LEFT JOIN

CN00300 ON CN20100.NOTEINDX = CN00300.NOTEINDX

WHERE (RM20101.CURTRXAM > '0.00') AND (RM20101.DOCDATE > '9/30/2009') AND (RM20101.ORTRXAMT >= RM20101.CURTRXAM)

Thanks,

WayneS SSC Guru Points: 95460 More actions · Answer 5

kbnyny (9/7/2010)
Craig, I'm not sure I understand what you are saying when you say if I do the left join on the two tables, I will see a Null's on the fields that has record. That is exactly what I want to to show. Any recored that does not have value in to show Null.

Try this:

SELECT RM20101.DOCDATE AS bldat, RM20101.POSTDATE, RM20101.DOCNUMBR, RM00101.CUSTNAME, RM20101.CURNCYID, RM20101.ORTRXAMT,

CASE WHEN RM20101.ORTRXAMT = RM20101.CURTRXAM THEN 0 ELSE RM20101.CURTRXAM END AS 'Remaining Amt', RM20101.ORTRXAMT,

RM20101.CUSTNMBR, SOP10106.USRTAB09 AS xref1, CN20100.NOTEINDX, CN00300.TXTFIELD

FROM RM20101 INNER JOIN

SOP10106 ON RM20101.TRXDSCRN = SOP10106.SOPNUMBE INNER JOIN

RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR LEFT JOIN

CN20100 ON RM20101.CUSTNMBR = CN20100.CUSTNMBR AND RM20101.DOCNUMBR = CN20100.DOCNUMBR LEFT JOIN

CN00300 ON CN20100.NOTEINDX = CN00300.NOTEINDX

WHERE (RM20101.CURTRXAM > '0.00') AND (RM20101.DOCDATE > '9/30/2009') AND (RM20101.ORTRXAMT >= RM20101.CURTRXAM)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

kbnyny Ten Centuries Points: 1038 More actions · Answer 6

kbnyny

Ten Centuries

Points: 1038

September 7, 2010 at 11:35 am

#1217496

Thanks Wayne it work perfect. 😀

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

Try this (note the two left joins now)

SELECT

RM20101.DOCDATE AS bldat,

RM20101.POSTDATE,

RM20101.DOCNUMBR,

RM00101.CUSTNAME,

RM20101.CURNCYID,

RM20101.ORTRXAMT,

CASE WHEN RM20101.ORTRXAMT = RM20101.CURTRXAM THEN 0 ELSE RM20101.CURTRXAM END AS 'Remaining Amt',

RM20101.ORTRXAMT,

RM20101.CUSTNMBR,

SOP10106.USRTAB09 AS xref1,

CN20100.NOTEINDX,

CN00300.TXTFIELD

FROM

RM20101

INNER JOIN

SOP10106

ONRM20101.TRXDSCRN = SOP10106.SOPNUMBE

INNER JOIN

RM00101

ONRM20101.CUSTNMBR = RM00101.CUSTNMBR

LEFT JOIN

CN20100

ONRM20101.CUSTNMBR = CN20100.CUSTNMBR

AND RM20101.DOCNUMBR = CN20100.DOCNUMBR

LEFT JOIN

CN00300

ONCN20100.NOTEINDX = CN00300.NOTEINDX

WHERE

(RM20101.CURTRXAM > '0.00')

AND (RM20101.DOCDATE > '9/30/2009')

AND (RM20101.ORTRXAMT >= RM20101.CURTRXAM)

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA