Thread: Question Join/Subselect
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
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 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 majordomo@postgresql.org > > > >
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: >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; >>> >>> > > >
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: >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? > > >