how to create this view?

[JJHN]

I have 2 tables like this:
table1:
col1 | col2 | col3
a | A | 1
b | A | 2
b | A | 3
c | B | 2
...

table:
col1 | col2|
A | 1
A | 2
A | 3
A | 4
A | 5
B | 1
B | 2
B | 3
....

I'd liek to create a view to generate:
t1.col1 | t2.col1 | t2.col2
a | A | 2
a | A | 3
a | A | 4
a | A | 5
b | A | 1
b | A | 4
b | A | 5
c | B | 1
c | B | 3

pls teach me the sql statement.thank you

 

[angiole]

Your results don't seem to be consistent with the data in the tables.
Please clarifiy. AA 8~)

 

[JJHN]

Thank you,
OK,let me clarifiy.
look at first row (which col1 is a) in table1,it refer to A in table2,then in the view,it get all of rows in table2 which col1 is A and col2 is not 1

look at 2nd and 3rd rows(which col1 is b)in table1,it refer to A in table2,then in the view,it get all of rows in table2 which col1 is A and col2 is not 2 or 3

look at 4th row(which col1 is c)in table1,it refer to B in table2,then in the view,it get all of rows in table2 which col1 is B and col2 is not 2.

 

[angiole]

Well...

The following SQL will provide the correct results for t1 'a' and 'c'.

The problem with 'b' is that there are 2 sets of retrievals for b and A, they are including and excluding each other at the same time.

Your real data may not have this problem.

SELECT T1.COL1,
T2.COL1,
T2.COL2
FROM Table1 T1,
Table2 T2
WHERE T1.COL2=T2.COL1
AND T1.COL3 <> T2.COL2
ORDER BY
1,2,3;

Cheers
AA 8~)

 

[JJHN]

Thank you.
the requeirment is:even a or c is not appear in table1 only once,user can add more rows