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

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?

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

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)

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)

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