2 Replies Latest reply: Dec 7, 2012 6:19 PM by 929370 RSS

    Need help with the query

    929370
      Hi experts,

      I am writing a query to give me report based on CustomerID. I will give you the tables that I am trying to get the query from.

      1. Customer table:
      Custid (pk)
      memberid
      fname
      lname
      bname

      2. Cust_cokeid table:
      Cokeid (pk, fk)
      Custid (pk, fk)

      3. Cokeid table:
      Cokeid (pk)

      4. Coke_Rebate table:
      Cokeid (fk)
      TotalNCB
      TotalCSD

      5. Cust_Drpepid table:
      Drpepid (pk, fk)
      Custid (pk, fk)

      6. Drpepid table:
      Drpepid (pk)

      7. Drpepper_rebate table:
      Drpepid (fk)
      TotalNCB
      TotalCarb

      Here is the query I have wrote:

      select CUSTOMERS.CUSTID as CUSTID,
      CUSTOMERS.MEMBERID as MEMBERID,
      CUSTOMERS.BNAME as BNAME,
      DRPEPPER_REBATE.DRPEPID as DRPEPID,
      DRPEPPER_REBATE.TOTALCARB as TOTALCARB,
      DRPEPPER_REBATE.TOTALNCB as TOTALNCB,
      COKE_REBATE.COKEID as COKEID,
      COKE_REBATE.TOTALNCB as TOTALNCB,
      COKE_REBATE.TOTALCSD as TOTALCSD
      from DRPEPPER_REBATE DRPEPPER_REBATE,
      CUST_DRPEPID CUST_DRPEPID,
      DRPEPID DRPEPID,
      CUST_COKEID CUST_COKEID,
      COKE_REBATE COKE_REBATE,
      COKEID COKEID,
      CUSTOMERS CUSTOMERS
      where COKEID.COKEID=CUST_COKEID.COKEID
      and CUST_COKEID.CUSTID(+) =CUSTOMERS.CUSTID
      and COKEID.COKEID=COKE_REBATE.COKEID(+)
      and CUST_DRPEPID.CUSTID(+) =CUSTOMERS.CUSTID
      and CUST_DRPEPID.DRPEPID=DRPEPID.DRPEPID
      and DRPEPID.DRPEPID=DRPEPPER_REBATE.DRPEPID(+)


      I have more then 700 customer records in the table, but so far I can only pull 500 records. Not sure what I am doing wrong, but I need serious help. Its been more then 2 days I am trying to fix this, but not sure what I am missing.

      Reason I am trying to pull all the records from the customer table is because I want to find out who is missing cokeid and Drpepid.
        • 1. Re: Need help with the query
          Frank Kulash
          Hi,

          Welcome to the forum!

          Whenever you have a problem, please post a complete script that people can run to re-create the problem and test their ideas. Include CREATE TABLE and INSERT statements for a little sample data, in this case including some of the rows you know you are missing. Post the correct results you want form that data. Explain how you get those results from that data.

          It looks like you're doing an outer join here. You want to see all rows from customers and matching rows prom cust_drpepid, but, if there are no matchng rows, you want to see the rows from customers anyway. So you're correctly saying:
          where   ...
          and     CUST_DRPEPID.CUSTID(+) = CUSTOMERS.CUSTID
          When a row in customers does not have a matching row in cust_drpepid, then the row will still appear, but all the columns form cust_drpepid will be NULL.
          Now look what you're doing next:
          and     CUST_DRPEPID.DRPEPID = DRPEPID.DRPEPID
          That's an inner join, and it cancels the effect of the previous outer join. That is, if a customer did not have a matching row in cust_drpepid, then all the columns from cust_drpepid will be NULL, including the drpepid column, and that means the join condition to drpepid won't be TRUE. You probably need to make this an outer join:
          and     CUST_DRPEPID.DRPEPID = DRPEPID.DRPEPID (+)
          Since one join condition has a + sign after cust_drpepid, then all conditions involving cust_drpepid probably need a + sign somewhere.

          The same thing applies to cust_cokeid and cokeid:
          where   COKEID.COKEID =CUST_COKEID.COKEID (+)
          Of course, since I can't re-create the problem, I can't tell if this solves it or not.

           

          This question is a SQL question; it doesn't have anything to do with SQL*Plus. In the future, you'll get better replies if you post questions like this in the SQL and PL/SQL
          The forum FAQ can help you a lot: {message:id=9360002}
          • 2. Re: Need help with the query
            929370
            Hi,

            Thanks for your reply. You explained me very well. I actually solved the problem with full joined where it pulls all the records. I will also try your solution see how it works :)