Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    27

    Angry Unanswered: left outer join in a view.

    I want a view to show all jobs. And if they have a quote to show these details.
    Here is a stripped down version:

    CREATE OR REPLACE VIEW cvw AS
    SELECT J.reference AS RefNumber,
    Q1.set_type AS QuoteType
    FROM JOBS J
    LEFT OUTER JOIN QUOTES Q1 ON (
    Q1.reference = J.reference
    AND Q1.seq = ( SELECT max(Q2.seq)
    FROM QUOTES Q2
    WHERE Q2. reference = J.reference));


    Query 1
    SQL> select QuoteType, count(*) from cvw
    group by QuoteType;

    S COUNT(*)
    - ----------
    F 20
    V 3
    743

    BUT I can't query on the QuoteType column.....

    Query 2
    SQL> select count(*) from cvw
    where QuoteType = 'F';

    COUNT(*)
    ----------
    766

    Why? Why? Why?

    If I try it with a regular JOIN, it works OK but of course that's not what I want.
    Any ideas?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    outer join dood.

    it's adding all the counts together since (through your view) you are asking for F and everything that is not F.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •