Solved

sql join

Posted on 2011-09-21
6
263 Views
Last Modified: 2012-05-12
i seem to be getting two records back when i should only be getting one.
the problem is with --CALLS BETWEEN 2ND AND 3RD CHASE LETTERS--
select ndwq1.*
        , rl.first_letter_sent
        , rl.second_letter_sent
        , rl.third_letter_sent

FROM load_bill_fred p

LEFT OUTER JOIN 
       load_bill_reminder_letter rl
    ON rl.fred_no = p.fred_no
   AND rl.fred_seq_no = p.fred_seq_no
    AND rl.rec_type ='elto'       

INNER JOIN
       load_bill_fred_entity pe
    ON p.fred_no = pe.fred_no

INNER JOIN 
       load_bill_vz_name vn
    ON vn.id_no = pe.cust_no

INNER JOIN
       load_bill_fred_line pl
    ON pl.fred_no = p.fred_no
   AND pl.fred_seq_no = p.fred_seq_no
   AND pl.product_line_id 
IN 
(
	SELECT 
		RESULT4 
		FROM 
			Load_bill_TARIFF_RATING_G trg1
		WHERE trg1.STRUCTURE_KEY = 'Z_STRUC'
			AND trg1.TYPE = 'EL_LINE_ID'
			AND trg1.STATUS = 'P'
			AND trg1.ARG1 = pe.prod_id
			AND trg1.VERSION = 
		(
	SELECT MAX(trg2.VERSION)
		FROM Load_bill_TARIFF_RATING_G trg2
		WHERE trg2.STRUCTURE_KEY = trg1.STRUCTURE_KEY
			AND trg2.TYPE = trg1.TYPE
			AND trg2.STATUS = trg1.STATUS

) 

)

  -- GET THE MOBILE PHONE NUMBER
LEFT JOIN vw_Mobile_Phone_No mp
         ON mp.name_id_no   = pe.cust_no 

-- GET THE FAX PHONE NUMBER
LEFT JOIN vw_Fax_Phone_No fp
         ON fp.name_id_no   = pe.cust_no 

--CALLS AFTER 3RD CHASE LETTERS
LEFT OUTER JOIN  
       numbers_dialled_web_quote ndwq1
    ON ndwq1.dateid BETWEEN rl.first_letter_sent AND rl.second_letter_sent

   AND 
       (-- Quote Phone No
          ndwq1.ndbo =     '9'       + dbo.fn_parse_phone(vn.phone_home)
       OR ndwq1.ndbo = '91471'       + dbo.fn_parse_phone(vn.phone_home)

          -- Contact Phone Number
       OR ndwq1.ndbo =     '9'       + dbo.fn_parse_phone(mp.mobile_phone_no)
       OR ndwq1.ndbo = '91471'       + dbo.fn_parse_phone(mp.mobile_phone_no)

          -- Contact Alt Phone Number
       OR ndwq1.ndbo =     '9'       + dbo.fn_parse_phone(fp.fax_phone_no)
       OR ndwq1.ndbo = '91471'       + dbo.fn_parse_phone(fp.fax_phone_no)

       )


--CALLS BETWEEN 2ND AND 3RD CHASE LETTERS

LEFT OUTER JOIN   
       numbers_dialled_web_quote ndwq2
    ON ndwq2.dateid BETWEEN rl.second_letter_sent AND rl.third_letter_sent

   AND 
       (-- Quote Phone No
          ndwq2.ndbo =     '9'       + dbo.fn_parse_phone(vn.phone_home)
       OR ndwq2.ndbo = '91471'       + dbo.fn_parse_phone(vn.phone_home)

--           Contact Phone Number
       OR ndwq2.ndbo =     '9'       + dbo.fn_parse_phone(mp.mobile_phone_no)
       OR ndwq2.ndbo = '91471'       + dbo.fn_parse_phone(mp.mobile_phone_no)

--           Contact Alt Phone Number
       OR ndwq2.ndbo =     '9'       + dbo.fn_parse_phone(fp.fax_phone_no)
       OR ndwq2.ndbo = '91471'       + dbo.fn_parse_phone(fp.fax_phone_no)

       )

       
  where p.suc_seq_no IS NULL
   AND p.fred_status = 'P'
   AND p.cover_end_date > GETDATE()
   AND vn.ern IS NULL
   AND vn.ern_exempt IS NULL

   and p.fred_no = 20192364

Open in new window

0
Comment
Question by:aneilg
  • 3
  • 2
6 Comments
 
LVL 25

Assisted Solution

by:jogos
jogos earned 83 total points
ID: 36574977
With each join the risk that you get an duplication off records increases, there are always people with 2 adresses, 2 faxes , ....
0
 
LVL 6

Accepted Solution

by:
ianmills2002 earned 167 total points
ID: 36577596
How you join tables together is dependent on the relationship between the tables. Are they 1 to 1, 1 to many. For the 1 to many relationships, if you only ever what one row from that table, you need to put an additional condition on the table you are joining to to restrict it to the row you want. For example, if you want the most recent row, then you need to add a condition to find the MAX(date).

Looking at the name of the table and the join (below), it make perfect sense that more than one call could be make between 2 dates.

numbers_dialled_web_quote ndwq2
    ON ndwq2.dateid BETWEEN rl.second_letter_sent AND rl.third_letter_sent
0
 

Author Comment

by:aneilg
ID: 36578886
hello, if i comment out each either of the last outer joins i get one result. looking at the tables their is only one table that should be coming back.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 6

Assisted Solution

by:ianmills2002
ianmills2002 earned 167 total points
ID: 36584475
Just as a rule, I always put my LEFT OUTER JOIN to tables after all my INNER JOINs

If you have checked the "--CALLS BETWEEN 2ND AND 3RD CHASE LETTERS" join by running the query separately, and you find only 1 row then I would lean towards there being a problem with the logic where in this last join, you are joining on a range, and you have a number or OR's in your condition. Without knowing the data, my first guess might be that the one or more of the phone number columns may be blank and causing a false true in the logic.

What I do to iron out issues like this it to change the SELECT to show me all the columns of each table. I check 2 or 3 tables at a time to see if I can find which table join is causing the duplicate data.

Sorry I can't be more precise with the cause.
0
 

Author Comment

by:aneilg
ID: 36585736
thanks guys i think i was more to do with the logic.
0
 

Author Closing Comment

by:aneilg
ID: 36585741
only partially answered
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
possible to record changes (trigger I think) msql 11 34
SQL query with cast 38 42
Server 2012 management. 5 23
VBScript Write Column Headers 3 36
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question