This discussion is archived
2 Replies Latest reply: Dec 7, 2012 4:19 PM by 929370 RSS

Need help with the query

929370 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points