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 ;

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

---

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 .