Обсуждение: Join question
Question, I have a table (1) with 2 col (a & b) where b can sometimes be null. I need a query that if B is null I get back the contentsof A.. but if B is not null I do a join on it and table (2) col c to get the contents of table (2) col d.. I cando this in multiple queries.. but was wondering if I could do it in 1. Travis
Does using a union count as one query? select a from tst_1 where b is null union select d from tst_2 t2 join tst_1 t1 on (t1.b = t2.c) where t1.b is NOT null On Thu, Aug 28, 2003 at 05:55:27PM -0400, Williams, Travis L, NEO wrote: > Question, > > I have a table (1) with 2 col (a & b) where b can sometimes be null. I > need a query that if B is null I get back the contents of A.. but if B > is not null I do a join on it and table (2) col c to get the contents > of table (2) col d.. I can do this in multiple queries.. but was > wondering if I could do it in 1.
Sure.. if it works.. I'm just trying to not have to make multiple calls to the DB.. I'll try it out.. Thanks, Travis -----Original Message----- From: Thomas A. Lowery [mailto:tlowery@stlowery.net] Sent: Thursday, August 28, 2003 8:06 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Join question Does using a union count as one query? select a from tst_1 where b is null union select d from tst_2 t2 join tst_1 t1 on (t1.b = t2.c) where t1.b is NOT null On Thu, Aug 28, 2003 at 05:55:27PM -0400, Williams, Travis L, NEO wrote: > Question, > > I have a table (1) with 2 col (a & b) where b can sometimes be null. I > need a query that if B is null I get back the contents of A.. but if B > is not null I do a join on it and table (2) col c to get the contents > of table (2) col d.. I can do this in multiple queries.. but was > wondering if I could do it in 1. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
That would probably work great.. except I screwed my question up.. Let me try again and this time think before I write I have a table1 with 2 col (a & b) where b can sometimes be null. I need a query that if B is null I get back the contents of A.. but if B is not null I do a "select d from table2 where d like '%b%'" There is nothing to join between table1 & table2 (unless you can join on likes ;) ). but thanks for showing me unions.. (I just learned about joins).. more knowledge to help me on my way! Thanks, Travis -----Original Message----- From: Thomas A. Lowery [mailto:tlowery@stlowery.net] Sent: Thursday, August 28, 2003 8:06 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Join question Does using a union count as one query? select a from tst_1 where b is null union select d from tst_2 t2 join tst_1 t1 on (t1.b = t2.c) where t1.b is NOT null On Thu, Aug 28, 2003 at 05:55:27PM -0400, Williams, Travis L, NEO wrote: > Question, > > I have a table (1) with 2 col (a & b) where b can sometimes be null. I > need a query that if B is null I get back the contents of A.. but if B > is not null I do a join on it and table (2) col c to get the contents > of table (2) col d.. I can do this in multiple queries.. but was > wondering if I could do it in 1. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Thu, Aug 28, 2003 at 09:46:34PM -0400, Williams, Travis L, NEO wrote: > That would probably work great.. except I screwed my question up.. Let > me try again and this time think before I write > > I have a table1 with 2 col (a & b) where b can sometimes be null. I > need a query that if B is null I get back the contents of A.. but if B > is not null I do a "select d from table2 where d like '%b%'" There is > nothing to join between table1 & table2 (unless you can join on likes ;) > ). So the second select uses the value of table1 column b in the like condition? > -----Original Message----- > From: Thomas A. Lowery [mailto:tlowery@stlowery.net] > Sent: Thursday, August 28, 2003 8:06 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Join question > > > Does using a union count as one query? > > select a from tst_1 where b is null > union > select d from tst_2 t2 join tst_1 t1 on (t1.b = t2.c) > where t1.b is NOT null > > > On Thu, Aug 28, 2003 at 05:55:27PM -0400, Williams, Travis L, NEO wrote: > > Question, > > > > I have a table (1) with 2 col (a & b) where b can sometimes be null. I > > need a query that if B is null I get back the contents of A.. but if B > > is not null I do a join on it and table (2) col c to get the contents > > of table (2) col d.. I can do this in multiple queries.. but was > > wondering if I could do it in 1.
Yeah.. so that's why I didn't know if I could do it all in the same statement. Travis -----Original Message----- From: Thomas A. Lowery [mailto:tlowery@stlowery.net] Sent: Thursday, August 28, 2003 8:54 PM To: Williams, Travis L, NEO Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Join question On Thu, Aug 28, 2003 at 09:46:34PM -0400, Williams, Travis L, NEO wrote: > That would probably work great.. except I screwed my question up.. Let > me try again and this time think before I write > > I have a table1 with 2 col (a & b) where b can sometimes be null. I > need a query that if B is null I get back the contents of A.. but if B > is not null I do a "select d from table2 where d like '%b%'" There is > nothing to join between table1 & table2 (unless you can join on likes ;) > ). So the second select uses the value of table1 column b in the like condition? > -----Original Message----- > From: Thomas A. Lowery [mailto:tlowery@stlowery.net] > Sent: Thursday, August 28, 2003 8:06 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Join question > > > Does using a union count as one query? > > select a from tst_1 where b is null > union > select d from tst_2 t2 join tst_1 t1 on (t1.b = t2.c) > where t1.b is NOT null > > > On Thu, Aug 28, 2003 at 05:55:27PM -0400, Williams, Travis L, NEO wrote: > > Question, > > > > I have a table (1) with 2 col (a & b) where b can sometimes be null. I > > need a query that if B is null I get back the contents of A.. but if B > > is not null I do a join on it and table (2) col c to get the contents > > of table (2) col d.. I can do this in multiple queries.. but was > > wondering if I could do it in 1.
On Thursday, August 28, 2003, at 09:03 PM, Williams, Travis L, NEO
wrote:
>> I have a table1 with 2 col (a & b) where b can sometimes be null. I
>> need a query that if B is null I get back the contents of A.. but if B
>> is not null I do a "select d from table2 where d like '%b%'" There is
>> nothing to join between table1 & table2 (unless you can join on likes
>>
You can do something like this, but I can't promise any great
performance:
select case when b is null
then a
else (select d from table2 where d ~* b) end as
value
from table1;
jmelloy=# select * from table1;
a | b
---+------
1 |
2 |
3 |
4 | for
5 | asdf
6 | coo
(6 rows)
jmelloy=# select * from table2;
d
----------
forsythe
manasdf
cool
(3 rows)
jmelloy=# select case when b is null then a::varchar else
jmelloy-# (select d from table2 where d ~* b) end as value
jmelloy-# from table1;
value
----------
1
2
3
forsythe
manasdf
cool
(6 rows)
btw.. thanks for all the help.
Travis
-----Original Message-----
From: Jeffrey Melloy [mailto:jmelloy@visualdistortion.org]
Sent: Friday, August 29, 2003 12:32 AM
To: Williams, Travis L, NEO
Cc: Thomas A. Lowery; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Join question
On Thursday, August 28, 2003, at 09:03 PM, Williams, Travis L, NEO
wrote:
>> I have a table1 with 2 col (a & b) where b can sometimes be null. I
>> need a query that if B is null I get back the contents of A.. but if
B
>> is not null I do a "select d from table2 where d like '%b%'" There
is
>> nothing to join between table1 & table2 (unless you can join on likes
>>
You can do something like this, but I can't promise any great
performance:
select case when b is null
then a
else (select d from table2 where d ~* b) end as
value
from table1;
jmelloy=# select * from table1;
a | b
---+------
1 |
2 |
3 |
4 | for
5 | asdf
6 | coo
(6 rows)
jmelloy=# select * from table2;
d
----------
forsythe
manasdf
cool
(3 rows)
jmelloy=# select case when b is null then a::varchar else
jmelloy-# (select d from table2 where d ~* b) end as value
jmelloy-# from table1;
value
----------
1
2
3
forsythe
manasdf
cool
(6 rows)
Performace wise would I be better off just doing 2 query's.. i.e. select
a,b from table1.. then in perl I can check if b is not null and if is
isn't then I do a second query?
Travis
-----Original Message-----
From: Jeffrey Melloy [mailto:jmelloy@visualdistortion.org]
Sent: Friday, August 29, 2003 12:32 AM
To: Williams, Travis L, NEO
Cc: Thomas A. Lowery; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Join question
On Thursday, August 28, 2003, at 09:03 PM, Williams, Travis L, NEO
wrote:
>> I have a table1 with 2 col (a & b) where b can sometimes be null. I
>> need a query that if B is null I get back the contents of A.. but if
B
>> is not null I do a "select d from table2 where d like '%b%'" There
is
>> nothing to join between table1 & table2 (unless you can join on likes
>>
You can do something like this, but I can't promise any great
performance:
select case when b is null
then a
else (select d from table2 where d ~* b) end as
value
from table1;
jmelloy=# select * from table1;
a | b
---+------
1 |
2 |
3 |
4 | for
5 | asdf
6 | coo
(6 rows)
jmelloy=# select * from table2;
d
----------
forsythe
manasdf
cool
(3 rows)
jmelloy=# select case when b is null then a::varchar else
jmelloy-# (select d from table2 where d ~* b) end as value
jmelloy-# from table1;
value
----------
1
2
3
forsythe
manasdf
cool
(6 rows)
On Friday, August 29, 2003, at 12:36 AM, Williams, Travis L, NEO wrote: > Performace wise would I be better off just doing 2 query's.. i.e. > select > a,b from table1.. then in perl I can check if b is not null and if is > isn't then I do a second query? > > Travis > I would say it depends on the table distribution (% of b that are null, etc). At that point, you're just going to have to experiment, I think, and see what works best for you. My gut says that even including a subselect, it will be faster than checking in perl and issuing another query. But I have no idea, and the proper solution might change drastically based on your data. Jeff
about 5% of b are null.. any idea on how to word a query like what I'm looking for? I have no idea in SQL how to use the results of one select in another (I usually just do all of it in perl). Travis -----Original Message----- From: Jeffrey Melloy [mailto:jmelloy@visualdistortion.org] Sent: Friday, August 29, 2003 12:44 AM To: Williams, Travis L, NEO Cc: Thomas A. Lowery; pgsql-general@postgresql.org Subject: Re: [GENERAL] Join question On Friday, August 29, 2003, at 12:36 AM, Williams, Travis L, NEO wrote: > Performace wise would I be better off just doing 2 query's.. i.e. > select > a,b from table1.. then in perl I can check if b is not null and if is > isn't then I do a second query? > > Travis > I would say it depends on the table distribution (% of b that are null, etc). At that point, you're just going to have to experiment, I think, and see what works best for you. My gut says that even including a subselect, it will be faster than checking in perl and issuing another query. But I have no idea, and the proper solution might change drastically based on your data. Jeff