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
Solved

sql join

Posted on 2011-09-21
6
265 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 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