Question Join/Subselect

[Alex]

Hi,

I have some problems with creating a query that will replace values in
one table from another one.

Table 1:
userName : refCode1 : refCode2
------------------------------
alex : 12 : 24

Table 2:
refCode : ActualCode
---------------------
12 AAAAAA
24 BBBBBB
Result Desired
userName : refCode1 : refCode2
------------------------------
alex : AAAAAA : BBBBBB
I need to crete a view that returns me Table1 but replaces refCode1,
refCode2 with the ActualCode.

I did not have any success with Joins or Subselects so far.

Thanks for any advise

Alex

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Alex]

Andrew,
thanks for the help. The query actually works. However if I try to
create a view then the sever complains

Create Tabe: attribute "actualcode" duplicated;

Any Ideas on how to get around that ?

Alex

Andrew L. Gould wrote:

On Wednesday 27 August 2003 10:28 pm, Alex wrote:

Hi,

I have some problems with creating a query that will replace values in
one table from another one.

Table 1:
userName : refCode1 : refCode2
------------------------------
alex : 12 : 24

Table 2:
refCode : ActualCode
---------------------
12 AAAAAA
24 BBBBBB
Result Desired
userName : refCode1 : refCode2
------------------------------
alex : AAAAAA : BBBBBB
I need to crete a view that returns me Table1 but replaces refCode1,
refCode2 with the ActualCode.

I did not have any success with Joins or Subselects so far.

Thanks for any advise

Alex

I'm not sure how to handle the space in the table names. If there weren't any
spaces in table names, the following should work:

select Table1.userName, Table2.ActualCode, Table3.ActualCode
from Table1, Table2, Table2 as Table3
where Table1.refCode1 = Table2.refCode and Table1.refCode2 = Table3.refCode;

I hope this helps,

Andrew Gould
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Andrew L. Gould]

On Thursday 28 August 2003 02:20 am, Alex wrote:

Andrew L. Gould wrote:

On Wednesday 27 August 2003 10:28 pm, Alex wrote:

Hi,

I have some problems with creating a query that will replace values in
one table from another one.

Table 1:
userName : refCode1 : refCode2
------------------------------
alex : 12 : 24

Table 2:
refCode : ActualCode
---------------------
12 AAAAAA
24 BBBBBB
Result Desired
userName : refCode1 : refCode2
------------------------------
alex : AAAAAA : BBBBBB
I need to crete a view that returns me Table1 but replaces refCode1,
refCode2 with the ActualCode.

I did not have any success with Joins or Subselects so far.

Thanks for any advise

Alex

I'm not sure how to handle the space in the table names. If there weren't
any spaces in table names, the following should work:

select Table1.userName, Table2.ActualCode, Table3.ActualCode
from Table1, Table2, Table2 as Table3
where Table1.refCode1 = Table2.refCode and Table1.refCode2 =
Table3.refCode;

I hope this helps,

Andrew Gould

Andrew,
thanks for the help. The query actually works. However if I try to
create a view then the sever complains

Create Tabe: attribute "actualcode" duplicated;

Any Ideas on how to get around that ?

Alex

Alex,

Have you tried renaming the column being duplicated?

select Table1.userName, Table2.ActualCode as ActualCode1, Table3.ActualCode as
ActualCode2
from Table1, Table2, Table2 as Table3
where Table1.refCode1 = Table2.refCode and Table1.refCode2 =
Table3.refCode;

Best of luck,

Andrew Gould

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Richard Huxton]

On Thursday 28 August 2003 08:20, Alex wrote:

Andrew,
thanks for the help. The query actually works. However if I try to
create a view then the sever complains

Create Tabe: attribute "actualcode" duplicated;
Alias the output names:
.... Table2.ActualCode as actual1, Table3.ActualCode as actual2 ...
Any Ideas on how to get around that ?

Alex

Andrew L. Gould wrote:

On Wednesday 27 August 2003 10:28 pm, Alex wrote:

I'm not sure how to handle the space in the table names. If there weren't
any spaces in table names, the following should work:

select Table1.userName, Table2.ActualCode, Table3.ActualCode
from Table1, Table2, Table2 as Table3
where Table1.refCode1 = Table2.refCode and Table1.refCode2 =
Table3.refCode;

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Alex]

Thanks for the help.
Alex

Richard Huxton wrote:

On Thursday 28 August 2003 08:20, Alex wrote:

Andrew,
thanks for the help. The query actually works. However if I try to
create a view then the sever complains

Create Tabe: attribute "actualcode" duplicated;

Alias the output names:
... Table2.ActualCode as actual1, Table3.ActualCode as actual2 ...

Any Ideas on how to get around that ?

Alex

Andrew L. Gould wrote:

On Wednesday 27 August 2003 10:28 pm, Alex wrote:

I'm not sure how to handle the space in the table names. If there weren't
any spaces in table names, the following should work:

select Table1.userName, Table2.ActualCode, Table3.ActualCode
from Table1, Table2, Table2 as Table3

where Table1.refCode1 = Table2.refCode and Table1.refCode2 =
Table3.refCode;

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Alex]

Hi,

I have a query where I want to filter out records from table_a if a
field in table_a matches in table table_b. Basically table_b defines the
filter.

If table_b however is empty i dont get any results

SELECT A.value_one FROM table_a AS A, table_b AS B WHERE A.value_two <>
B.value_two;
or
SELECT A.value_one FROM table_a AS A, table_b AS B WHERE A.value_two <>
B.value_two AND B.value_two NOTNULL;

Only work if the there is a value in table_b.
Could anyone tell me if there is a way to do that ?
Thanks a lot
Alex

PS: I'd like to thank here persons who reply rather than sending the
message per mail.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Stephan Szabo]

On Mon, 1 Sep 2003, Alex wrote:

Hi,

I have a query where I want to filter out records from table_a if a
field in table_a matches in table table_b. Basically table_b defines the
filter.

Well something like one of the following should work depending
on how you want to treat nulls and such (and performance varies in
postgresql by version for each of the options):

SELECT a.value_one FROM table_a AS A where NOT EXISTS
(select 1 from table_b AS B WHERE A.value_two=B.value_two);
SELECT a.value_one FROM table_a AS A where A.value_two NOT IN
(select value_two from table_b);
SELECT a.value_one FROM table_a AS A LEFT OUTER JOIN
table_b AS B ON (a.value_two=B.value_two) WHERE B.value_two IS NULL;
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Alex]

Ron,
the idea is to provide a table where users can define filters. But it
this table may be as well empty.

Alex

Ron Johnson wrote:

On Sun, 2003-08-31 at 11:56, Alex wrote:

Hi,

I have a query where I want to filter out records from table_a if a
field in table_a matches in table table_b. Basically table_b defines the
filter.

If table_b however is empty i dont get any results

SELECT A.value_one FROM table_a AS A, table_b AS B WHERE A.value_two <>
B.value_two;
or
SELECT A.value_one FROM table_a AS A, table_b AS B WHERE A.value_two <>
B.value_two AND B.value_two NOTNULL;

Only work if the there is a value in table_b.
Could anyone tell me if there is a way to do that ?

If the filter is empty, how can you filter anything?

PS: I'd like to thank here persons who reply rather than sending the
message per mail.

What does that mean?

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster