presence/absence - Mailing list pgsql-novice
| From | Bryan Nuse |
|---|---|
| Subject | presence/absence |
| Date | |
| Msg-id | D00517C6-B32E-4E0D-9AA9-3F25C25BC8B5@gmail.com Whole thread Raw |
| Responses |
Re: presence/absence
|
| List | pgsql-novice |
Greetings,
I'm trying to compare two views, both with the same three fields, where the rows of one view are a subset of the rows of the other. I want to produce a new view that shows each row in the larger of the two original views, and has a new column that has a value of 1 if the combination of values on that row occurs in the smaller of the original views, and a 0 if it doesn't.
I'm certainly a beginner SQL user, and the solution that occurred to me -- namely a CASE conditional statement to produce the fourth column in the new view-- has not worked, because mentioning the columns of the smaller source view in the CASE statement induces the same behavior as though all the columns in both the smaller and larger source views were mentioned in the SELECT list -- that is, all combinations of the values in the six fields in the larger and smaller source tables are tested by the CASE statement (which is pretty useless). I only want the particular rows in the larger view to be tested pairwise against those in the smaller.
This is what I tried, where the larger source view mentioned above is called "SWWA_list", and the smaller view (a subset of the rows of the larger) is "SWWA_t1". Each of the source views has the same three columns, called point_id, region, and foray.
CREATE VIEW bird_data."SWWA_pres" AS (
SELECT
DISTINCT l.point_id, l.region, CASE WHEN (l.point_id, l.region, l.foray) = (t1.point_id, t1.region, t1.foray) THEN 1 ELSE 0 END
FROM "SWWA_list" l, "SWWA_t1" t1
ORDER BY l.point_id, l.region
);
What happens, when I try the above, is that rows that do appear in both source views produce 2 rows in the newly created view, identical except that one row has a 1 in the CASE column, and the other a 0. Rows that don't appear in both source show up just once in the new view, and have a 0 in the CASE column. Diagnosis of this (e.g., by removing the DISTINCT keyword) has proved to me that the CASE statement is considering all possible combinations of the values in the six columns from the two original views.
I tried specifying a ROW constructor, to force PostgreSQL to stop essentially permuting my column orders, but to no avail -- it seemed to change nothing:
CREATE VIEW bird_data."SWWA_pres" AS (
SELECT
DISTINCT l.point_id, l.region CASE WHEN ROW(l.point_id, l.region, l.foray) = ROW(t1.point_id, t1.region, t1.foray) THEN 1 ELSE 0 END
FROM "SWWA_list" l, "SWWA_t1" t1
ORDER BY l.point_id, l.region
);
So, my hunch is that there should be a very simple way to get what I'm after, but I've not been able to find it. Any advice would be most appreciated. If my description of the problem is too vague, I can send more specifics. Thanks in advance.
Bryan Nuse
pgsql-novice by date: