Solved

sql join

Posted on 2011-09-21
6
256 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now