Solved

sql join

Posted on 2011-09-21
6
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 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