Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

sql join

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
aneilg
Asked:
aneilg
  • 3
  • 2
3 Solutions
 
jogosCommented:
With each join the risk that you get an duplication off records increases, there are always people with 2 adresses, 2 faxes , ....
0
 
ianmills2002Commented:
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
 
aneilgAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ianmills2002Commented:
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
 
aneilgAuthor Commented:
thanks guys i think i was more to do with the logic.
0
 
aneilgAuthor Commented:
only partially answered
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now