Обсуждение: Select from second table only if select from first returns no results
Hello all,
I'm facing the following problem.
I have a database with two tables, t1 and t2. The two tables have the same simple structure, say columns col1, col2. What I want to do is to select from t1 and, in case no results are returned, to then select from t2.
Right now, all I'm doing is
SELECT col1 FROM t1 WHERE col2 = "STH"
UNION
SELECT col1 FROM t2 WHERE col2 = "STH";
That is, as far as I know, I always check both t1 and t2 and, moreover, I get no guarrantee that t1 entries will come first in the list of the results.
Any ideas on how to solve this?
P. Thespis
I'm facing the following problem.
I have a database with two tables, t1 and t2. The two tables have the same simple structure, say columns col1, col2. What I want to do is to select from t1 and, in case no results are returned, to then select from t2.
Right now, all I'm doing is
SELECT col1 FROM t1 WHERE col2 = "STH"
UNION
SELECT col1 FROM t2 WHERE col2 = "STH";
That is, as far as I know, I always check both t1 and t2 and, moreover, I get no guarrantee that t1 entries will come first in the list of the results.
Any ideas on how to solve this?
P. Thespis
In response to Petros Thespis : > Hello all, > > I'm facing the following problem. > > I have a database with two tables, t1 and t2. The two tables have the same > simple structure, say columns col1, col2. What I want to do is to select from > t1 and, in case no results are returned, to then select from t2. > > Right now, all I'm doing is > > SELECT col1 FROM t1 WHERE col2 = "STH" > UNION > SELECT col1 FROM t2 WHERE col2 = "STH"; > > That is, as far as I know, I always check both t1 and t2 and, moreover, I get > no guarrantee that t1 entries will come first in the list of the results. > > Any ideas on how to solve this? You can add an extra column containing the table-name: test=*# select * from t1; a | b ---+--- (0 rows) test=*# select * from t2; a | b ---+--- 2 | 2 (1 row) test=*# select 't1' as tab, * from t1 union all select 't2', * from t2; tab | a | b -----+---+--- t2 | 2 | 2 (1 row) But that's not a full solution for you. I think, you can write a simple function in plpgsql to solve your problem. Do you need more help? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
In response to A. Kretschmer : > In response to Petros Thespis : > > Hello all, > > > > I'm facing the following problem. > > > > I have a database with two tables, t1 and t2. The two tables have the same > > simple structure, say columns col1, col2. What I want to do is to select from > > t1 and, in case no results are returned, to then select from t2. > > > > Right now, all I'm doing is > > > > SELECT col1 FROM t1 WHERE col2 = "STH" > > UNION > > SELECT col1 FROM t2 WHERE col2 = "STH"; > > > > That is, as far as I know, I always check both t1 and t2 and, moreover, I get > > no guarrantee that t1 entries will come first in the list of the results. > > > > Any ideas on how to solve this? > > You can add an extra column containing the table-name: > > test=*# select * from t1; > a | b > ---+--- > (0 rows) > > test=*# select * from t2; > a | b > ---+--- > 2 | 2 > (1 row) > > test=*# select 't1' as tab, * from t1 union all select 't2', * from t2; > tab | a | b > -----+---+--- > t2 | 2 | 2 > (1 row) > > > But that's not a full solution for you. I think, you can write a simple > function in plpgsql to solve your problem. Do you need more help? Simple example: test=# create or replace function t1_or_t2(out a int, out b int) returns setof record as $$declare c int; begin perform * from t1; if found then return query select * from t1; else return query select * from t2; end if; end; $$ language plpgsql; CREATE FUNCTION test=*# select * from t1_or_t2(); a | b ---+--- 2 | 2 (1 row) test=*# insert into t1 values (1,1); INSERT 0 1 test=*# select * from t1_or_t2(); a | b ---+--- 1 | 1 (1 row) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net