1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How do I eliminate extra lines from SQL results?

Discussion in 'SQL PL/SQL' started by Buckyman, Jan 17, 2011.

  1. Buckyman

    Buckyman Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Hi All
    this is my first post, be gentle :)

    The SQL below works well, but the output generates repeated rows. The number of repeated rows is equivalent to the number of records in the table R_InterestArea.

    Thanks for any help

    Code (SQL):
    SELECT C_BPartner.Name, C_BPartner_Location.Phone, C_BPartner_Location.Fax, C_Location.Address1, C_Location.City || ', ' || C_Region.Description || ', ' || C_Location.Postal "CITYPROVPC"
    FROM C_BPartner
     JOIN AD_User
          ON (C_BPartner.C_BPartner_ID=AD_User.C_BPartner_ID AND AD_User.Name='.')
     JOIN R_ContactInterest
          ON (AD_User.AD_User_ID=R_ContactInterest.AD_User_ID)
     JOIN R_InterestArea
          ON (R_ContactInterest.R_InterestArea_ID=R_InterestArea.R_InterestArea_ID AND R_InterestArea.Name <> 'NN')
     LEFT OUTER JOIN C_BPartner_Location
          ON (C_BPartner.C_BPartner_ID=C_BPartner_Location.C_BPartner_ID AND C_BPartner_Location.Name='Head Office')
     LEFT OUTER JOIN C_Location
          ON (C_BPartner_Location.C_Location_ID=C_Location.C_Location_ID )
     JOIN C_Region
          ON (C_Location.C_Region_ID=C_Region.C_Region_ID)
    WHERE C_BPartner.ISCUSTOMER = 'Y' AND C_BPartner.IsActive = 'Y' ORDER BY  C_BPartner.Name ASC
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're including WHERE clause predicates in your JOIN conditions so move those to the WHERE clause. Try this instead and see if it works for you:

    Code (SQL):
     
    SELECT C_BPartner.Name, C_BPartner_Location.Phone, C_BPartner_Location.Fax, C_Location.Address1, C_Location.City || ', ' || C_Region.Description || ', ' || C_Location.Postal "CITYPROVPC"
    FROM C_BPartner
     JOIN AD_User
          ON (C_BPartner.C_BPartner_ID=AD_User.C_BPartner_ID)
     JOIN R_ContactInterest
          ON (AD_User.AD_User_ID=R_ContactInterest.AD_User_ID)
     JOIN R_InterestArea
          ON (R_ContactInterest.R_InterestArea_ID=R_InterestArea.R_InterestArea_ID )
     LEFT OUTER JOIN C_BPartner_Location
          ON (C_BPartner.C_BPartner_ID=C_BPartner_Location.C_BPartner_ID)
     LEFT OUTER JOIN C_Location
          ON (C_BPartner_Location.C_Location_ID=C_Location.C_Location_ID )
     JOIN C_Region
          ON (C_Location.C_Region_ID=C_Region.C_Region_ID)
    WHERE C_BPartner.ISCUSTOMER = 'Y'  AND AD_User.Name='.' AND C_BPartner.IsActive = 'Y' AND R_InterestArea.Name <> 'NN'  AND C_BPartner_Location.Name='Head Office' ORDER BY  C_BPartner.Name ASC

     
     
  3. Buckyman

    Buckyman Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Thanks David
    I did exactly as you suggested, no joy :(
    I get exactly the same results, exactly the same number of records.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It seems you missed out some more clause to add into your where clause to avoid duplicate result . But we wont be able to help you unless you provide create table scripts with with some sample data.
     
  5. Buckyman

    Buckyman Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    I don't know if I can provide table scripts. I'm not working with command line, I'm just a dopey sql jocky using using iReport & ooBase with Oracle plugin. Maybe I can explain...
    This is a mailing list. We need the address for every customer unless one of the contacts has a flag "NN" (no newsletter). I'll go through the first 3 joins (the repetition is related somehow to the 3rd join).
    Every customer has a default contact with name "." (constraint in the first join, table AD_User)
    AD_User is linked to a table that has interests for each contact, R_ContactInterest
    R_ContactInterest is linked to R_InterestArea which contains the 2 char. interest codes. We are constraining this join for records that are "<> 'NN'", but there could be other records with "CO", "NL" etc. The number of repeated rows in the report is equal to the number of records from the join. For instance, if the join connects three records with "CO", "NL", "NN", then the report will have 3 repeated lines.

    Make sense?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Perfectly and if you'll use SELECT DISTINCT in the outermost query you'll solve your problem.
     
  7. Buckyman

    Buckyman Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    I began the SQL with "SELECT DISTINCT..."
    and it worked!
    Thanks!
     
  8. Buckyman

    Buckyman Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Ah crap! I just realized the query is actually returning data for customers with interest = "NN". IOW, "<>'NN'" is being ignored.
    The plot thickens..., I removed "DISTINCT" to check the relationship between repeated rows and table R_InterestArea. If interest NN is not present, then (repeated rows) = (the number of interests for the contact). If interest NN is present, (repeated rows) = (the number of interests for the contact -1), IOW the existence of NN reduces the repeated rows, but does not stop the customer address from being included in the results.
    Argh!
     
  9. Buckyman

    Buckyman Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Think I figured it our on my own. I added a subquery for customers with interests ='NN'
    Then used that as a constraint with NOT IN. See below.
    Code (SQL):
    SELECT C_BPartner.Name, C_BPartner_Location.Phone, C_BPartner_Location.Fax, C_Location.Address1, C_Location.City || ', ' || C_Region.Description || ', ' || C_Location.Postal "CITYPROVPC"
    FROM C_BPartner
     LEFT OUTER JOIN C_BPartner_Location
          ON (C_BPartner.C_BPartner_ID=C_BPartner_Location.C_BPartner_ID AND C_BPartner_Location.Name='Head Office' AND C_BPartner.C_BPartner_ID NOT IN
    (
    SELECT AD_User.C_BPartner_ID
    FROM AD_User
     JOIN R_ContactInterest
          ON (AD_User.AD_User_ID = R_ContactInterest.AD_User_ID AND AD_User.Name = '.')
     JOIN R_InterestArea
          ON (R_ContactInterest.R_InterestArea_ID = R_InterestArea.R_InterestArea_ID AND R_InterestArea.Name = 'NN')
    )
    )
     LEFT OUTER JOIN C_Location
          ON (C_BPartner_Location.C_Location_ID=C_Location.C_Location_ID )
     JOIN C_Region
          ON (C_Location.C_Region_ID=C_Region.C_Region_ID)
    WHERE C_BPartner.ISCUSTOMER = 'Y' AND C_BPartner.IsActive = 'Y'

     ORDER BY  C_BPartner.Name ASC