joining views

[Lee2]

i have 2 views p1v,p2v

select * from p1v;
id1 | count1
-----+--------
1 | 2
2 | 1
(2 rows)

select * from p2v;
id2 | count2
-----+--------
1 | 1
2 | 1
(2 rows)

is it possible to join views ? i have incorrect results(see bellow. 2's in the column count2 ) how does it work? is it important, how the views were created, or are they considered tables during processing of the select?
database is Postgres7

select * from p1v,p2v;
id1 | count1 | id2 | count2
-----+--------+-----+--------
1 | 2 | 1 | 2
1 | 2 | 2 | 2
2 | 1 | 1 | 1
2 | 1 | 2 | 1
(4 rows)

 

[tlbroadbent]

You can Join views. In SQL they are treated just like tables except the there is no physical data content. Ddata content is created dynamically.

Modify your query to include JOIN criteria. As written, it is joining each record in p1v to each record in p2v.

Select Id1, Count1, Count2
From p1v, p2v
Where p1v.id1=p2v.id2

OR in ANSI SQL syntax...

Select Id1, Count1, Count2
From p1v Inner Join p2v
On p1v.id1=p2v.id2 Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Lee2]

hi, thank you but it
doesn't work for me

result:
id1 | count1 | count2
-----+--------+--------
1 | 2 | 2
2 | 1 | 1

it is wrong, because column count2 shoudln't contain any 2's

maybe an error in postgres

 

[carp]

I suspect you either misentered the SQL that tlbroadbent supplied or else your data is different than you think. Here are my results; the SQL appears to work perfectly!

SQL> select * from p1v;

ID1 COUNT1
---------- ----------
1 2
2 1

SQL> select * from p2v;

ID2 COUNT2
---------- ----------
1 1
2 1

SQL> select id1, count1, count2
2 from p1v, p2v
3 where id1 = id2;

ID1 COUNT1 COUNT2
---------- ---------- ----------
1 2 1
2 1 1

Is it possible that you entered "select id1, count1, count1..." by mistake?

 

[Lee2]

thank you very much. but it doesn't work anyway.

I will explain my problem form the beginning:

table order_item

id | name | state | order_id
----------------------------------
20 | item1 | 1 | 1
21 | item2 | 1 | 1
23 | item3 | 2 | 1
24 | item1 | 1 | 2
25 | item2 | 2 | 2

Now I create two views:
the first view will list order_id, and for each order_id also count of items with state 1;

create view p1v as select order_id as id1, count(id) as count1 from order_item where state=1 group by order_id;

Second view p2v, will list all order_id and for each order_id also count of items with state 2;

create view p2v as select order_id as id2, count(id) as count2 from order_item where state=2 group by order_id;

Now when i select from views, i have results:

select * from p1v;
id1 | count1
-----+--------
1 | 2
2 | 1
(2 rows)

select * from p2v;
id2 | count2
-----+--------
1 | 1
2 | 1
(2 rows)

but when i enter command

Select Id1, Count1, Count2
From p1v, p2v
Where p1v.id1=p2v.id2

i have result:

id1 | count1 | count2
-----+--------+--------
1 | 2 | 2
2 | 1 | 1

and that is incorrect.
and i can't explain it. how does it work?

bye

 

[carp]

Well, this IS a tough one. Here are the results I get:

=====================================================
SQL> select * from order_item;

STATE ORDER_ID
---------- ----------
1 1
1 1
2 1
1 2
2 2

SQL> select * from p1v;

ID1 COUNT1
---------- ----------
1 2
2 1

SQL> select * from p2v;

ID2 COUNT2
---------- ----------
1 1
2 1

SQL> select id1, count1, count2 from p1v, p2v where id1 = id2;

ID1 COUNT1 COUNT2
---------- ---------- ----------
1 2 1
2 1 1
===================================================
As you can see, I'm getting the correct results.

I defined my views a little differently than you did, but
it shouldn't make any difference (unless you have something odd going on with your id column!).

P1V:
select order_id as id1, count(*) as count1 from order_item where state=1 group by order_id;

P2V:
select order_id as id2, count(*) as count2 from order_item where state=2 group by order_id;

 

[Lee2]

it is impossible.
it doesn't work for me even if i created the views as you did.
what database are you using?
i am using PostgreSQL 7

maybe it is an bug in PostgreSQL

 

[carp]

Could be. I'm using Oracle.
You might want to check w/ PostgreSQL support folks on this one. What you are doing is straightforward and simple - it SHOULD work!

 

[Lee2]

Yeah. It was a bug. According to PostgreSQL support, it is fixed in newer version.

 

[AJ3]

I have joined many views and they have always joined just like tables (Sybase, Oracle, Informix, etc). I agree that your results are not what one would expect. I guess I would like to see the definition of the underlying tables and views, but what you describe above seems wrong. The fact that you provide no join condition should be irrelevant. A cartesian product is supposed to faithfully join every row on one side of the join with every row on the other side. Unless some sort of data modification is happening between query executions, what you describe seems very mysterious.

IN SYBASE:

create table x1 ( id1 int, count1 int )
create table x2 ( id2 int, count2 int )
go
insert into x1 values ( 1,2 )
insert into x1 values ( 2,1 )
insert into x2 values ( 1,1 )
insert into x2 values ( 2,1 )
go
create view v1 as select * from x1
create view v2 as select * from x2
go
select * from v1, v2
go

PRODUCES:

id1 count1 id2 count2
----------- ----------- ----------- -----------
1 2 1 1
1 2 2 1
2 1 1 1
2 1 2 1

-----------------------------------------------
How exactly is YOUR count2 defined? Is it a value calculated at execution time?

 

[carp]

As mentioned in the preceding post, it's a bug!