How can I get this query to print a record that only exists in one table?

Question

I am trying to create a query that will accept a date from the user and display information from two tables based on this date. This works for all of my tests except my last test. My last test, I enter a date that should return a record that only exists in the expmast table and does not exist in the expbycc table. When I enter the date to try and get this record to be returned, it tells me no records have been found. I know this is because in my where, i have an AND that checks if M.ExpNum = C.ExpNUm which isn;t true for this record because it only exists in one table. I can not figure out how to get this query to work. Any help/advice is greatly appreciated. Below is my script, followed by the table structures used for this query, thank you.

Script:

ACCEPT Date PROMPT 'Enter a date:';

SELECT M.ExpNum, EDate, IsCash, StoreCode, CashAmt, CType, CCNum, Amt
FROM ExpMast M, ExpByCc C
WHERE EDate = to_date('&Date','mm-dd-yy')
AND M.ExpNum = C.ExpNum;

Tables:

CREATE TABLE EXPMAST
   (ExpNum NUMBER(2,0) NOT NULL PRIMARY KEY,
    EDate DATE,
    IsCash VARCHAR2(1),
    StoreCode VARCHAR2(4),
    CONSTRAINT fk_STORE_EXPMAST FOREIGN KEY (StoreCode)
    REFERENCES STORE (Code)
);


CREATE TABLE ExpByCC
   (ExpNum NUMBER(2,0) NOT NULL,
    CType VARCHAR2(1) NOT NULL,
    CCNum VARCHAR2(16) NOT NULL,
    Amt DECIMAL(5,2),
    CONSTRAINT fk_CRCARD_ExpByCC FOREIGN KEY (CType, CCNum)
    REFERENCES CRCARD (CType, CCNum),
    CONSTRAINT fk_EXPMAST_ExpByCC FOREIGN KEY (ExpNum)
    REFERENCES EXPMAST (ExpNum),
    CONSTRAINT pk_ExpByCC PRIMARY KEY (ExpNum, CType, CCNum)
);

score 1 · Accepted Answer · answered Apr 28 '14 at 02:27

1

You need a left outer join. And you can't express an outer join using your implicit join syntax. You want to use explicit joins in the from clause.

A simple rule: NEVER use commas in the from clause.

Now, it is easy:

SELECT M.ExpNum, EDate, IsCash, StoreCode, CashAmt, CType, CCNum, Amt
FROM ExpMast M LEFT OUTER JOIN
     ExpByCc C
     ON M.ExpNum = C.ExpNum AND
WHERE M.EDate = to_date('&Date','mm-dd-yy') AND
      C.ExpNum IS NULL;

answered Apr 28 '14 at 02:27

Gordon Linoff

1,242,037
58
646
786

teacher isn't really doing a good job with teaching the joins. I had a db class at penn state when I was there and knew we used them but for what ever reason my teacher at my current college is doing it the way I had it so I was trying that way. Thank you though for the advice I am going to change my script and hopefully it works. Thanks again – Stc5097 Apr 28 '14 at 02:33
@user3255899 . . . I'm sorry you have a teach who gives poor advice. You, alas, are not the only one. – Gordon Linoff Apr 28 '14 at 02:36
As @Gordon says, use the `JOIN..ON..` style. – Michael Green Apr 28 '14 at 03:07

How can I get this query to print a record that only exists in one table?

1 Answers1