Results 1 to 3 of 3

Thread: help with joining table and view

  1. #1
    Join Date
    Feb 2010
    Posts
    3

    help with joining table and view

    Hi

    I hava a table (well) and a view (plate_view), for plate_view with pid = 1, there are supposed to be only 20 records, each of reach correponds to a record from the "well" table based on the well id. The well table has 96 different records with different well id.

    When I do a full outer join like following, why is it that I can only see 20 reocrds ? Shouldn't I expect to see 20 records and 76 records with null values appear for the 76 well records that cannot be found from the view of plate_view ?

    select a.well, b. b.content, b.well,
    from well_def a full outer join
    plate_view b
    on a.well_id = b.well_id
    where b.pid = 1 ;

  2. #2
    Join Date
    Apr 2009
    Posts
    86
    stong, the reason is the order in which the parts of SQL is processed. The order is:

    FROM clause
    WHERE clause
    GROUP BY clause
    HAVING clause
    ORDER BY clause
    SELECT clause

    NOTE: I am doing this from memory. I am fairly sure this is the correct order by may have an item out of order.

    The From clause if processed first. This is:
    Code:
    from well_def a full outer join
    plate_view b
    on a.well_id = b.well_id
    After this processes you will have an intermediate result set as you describe. Here is an example of the FULL OUTER JOIIN where the B table has rows that are not in the A table, the A table as values not in the B table and rows that are in both the A and B table:
    Code:
    B.PID B.CONTENT B.WELLID A.WELLID A.WELL
    1     VALUE     A        A        VALUE
    1     VALUE     B        B        VALUE
    1     VALUE     C        D        NULL
    2     VALUE     E        E        VALUE
    NULL  NULL      NULL     F        VALUE
    3     VALUE     G        NULL     NULL
    Next the WHERE clause is applied. This is WHERE B.PID = 1
    Code:
    B.PID B.CONTENT B.WELLID A.WELLID A.WELL
    1     VALUE     A        A        VALUE
    1     VALUE     B        B        VALUE
    1     VALUE     C        D        NULL
    As you can see this is NOT the result you are describing.

    What you need to do is either filter on B.PID =1 in the FROM clause or filter on the View before it is Joined.

    In the From CLAUSE:
    Code:
    SELECT A.WELL, B.CONTENT, B.WELL
    FROM WELL_DEF A
           FULL OUTER JOIN
             PLATE_VIEW B
             ON     A.WELL_ID = B.WELL_ID
                AND B.PID     = 1
    On the View:
    Code:
    SELECT A.WELL, B.CONTENT, B.WELL
    FROM WELL_DEF A
           FULL OUTER JOIN
             (SELECT CONTENT, WELL, WELL_ID
              FROM PLATE_VIEW
              WHERE B.PID = 1
             ) AS B
             ON A.WELL_ID = B.WELL_ID

  3. #3
    Join Date
    Feb 2010
    Posts
    3

    Thumbs up

    thanks ,SDas,

    You are right on. I use your suggestion with filtering on the View before it is Joined with my "well" table and the query give me back whas I was looking for (all 96 records with 76 empty values from the View)

    I should really remind myself to be more aware of the order in which SQL directives get executed .

Tags for this Thread

Posting Permissions

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