Question Join/Subselect

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

Andrew L. Gould wrote:

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

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

Thanks for the help.
Alex

Richard Huxton 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 ?

Thanks a lot
Alex

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

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;

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?

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"they love our milk and honey, but preach about another way of living"
Merle Haggard, "The Fighting Side Of Me"

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: