SELECT from multiple tables (not join though)

[Madison Kelly]

Hi all,

I have another question, I hope it isn't too basic. ^.^

I want to do a select from multiple tables but not join them. What I
am trying to do is something like this (though this doesn't work as I need):

SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a,
file_info_2 b, file_info_3 c WHERE a.file_name='/' AND
b.file_parent_dir='/' AND c.file_parent_dir='/';

That returns every combination of the results from the three tables
which is a huge number. What I need is to return all of the matches in
all of the tables in a single column. Once I have all the matches in one
column the next trick is to sort the combined results (any tips there?).

I hope the question was clear. Please let me know if it wasn't. Thanks!

Madison

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Madison Kelly]

Typo, that should have been:

SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a,
file_info_2 b, file_info_3 c WHERE a.file_parent_dir='/' AND
b.file_parent_dir='/' AND c.file_parent_dir='/';

(All the WHERE... are the same)

Madison

Madison Kelly wrote:

> Hi all,
>
> I have another question, I hope it isn't too basic. ^.^
>
> I want to do a select from multiple tables but not join them. What I
> am trying to do is something like this (though this doesn't work as I
> need):
>
> SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a,
> file_info_2 b, file_info_3 c WHERE a.file_name='/' AND
> b.file_parent_dir='/' AND c.file_parent_dir='/';
>
> That returns every combination of the results from the three tables
> which is a huge number. What I need is to return all of the matches in
> all of the tables in a single column. Once I have all the matches in one
> column the next trick is to sort the combined results (any tips there?).
>
> I hope the question was clear. Please let me know if it wasn't. Thanks!
>
> Madison
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo@postgresql.org[/email])

[John Sidney-Woollett]

I hope I've read your question properly - I seem to be giving answers to
un-asked questions lately! ;)

How about...

SELECT file_name from file_info_1 WHERE file_name='/'
union
SELECT file_name from file_info_2 WHERE file_parent_name='/'
union
SELECT file_name from file_info_3 WHERE file_parent_name='/'
order by file_name;

Does that do what you want?

John Sidney-Woollett

Madison Kelly wrote:

> Hi all,
>
> I have another question, I hope it isn't too basic. ^.^
>
> I want to do a select from multiple tables but not join them. What I
> am trying to do is something like this (though this doesn't work as I
> need):
>
> SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a,
> file_info_2 b, file_info_3 c WHERE a.file_name='/' AND
> b.file_parent_dir='/' AND c.file_parent_dir='/';
>
> That returns every combination of the results from the three tables
> which is a huge number. What I need is to return all of the matches in
> all of the tables in a single column. Once I have all the matches in one
> column the next trick is to sort the combined results (any tips there?).
>
> I hope the question was clear. Please let me know if it wasn't. Thanks!
>
> Madison
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [email]majordomo@postgresql.org[/email]

[Janning Vygen]

Am Montag, 10. Januar 2005 18:22 schrieb Madison Kelly:

> Hi all,
>
> I have another question, I hope it isn't too basic. ^.^
>
> I want to do a select from multiple tables but not join them. What I
> am trying to do is something like this (though this doesn't work as I
> need):
>
> SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a,
> file_info_2 b, file_info_3 c WHERE a.file_name='/' AND
> b.file_parent_dir='/' AND c.file_parent_dir='/';
>
> That returns every combination of the results from the three tables
> which is a huge number. What I need is to return all of the matches in
> all of the tables in a single column. Once I have all the matches in one
> column the next trick is to sort the combined results (any tips there?).

you want something like this

SELECT a.file_name
FROM file_info_1 a
WHERE a.file_name='/'

UNION

SELECT b.file_name
FROM file_info_2 b
WHERE b.file_name='/'

UNION

SELECT c.file_name
FROM file_info_3 c
WHERE c.file_name='/'

ORDER BY 1;

for further documentation visit

[url]http://www.postgresql.org/docs/7.4/interactive/sql-select.html[/url]

or your local postgresql documentation.

kind regards,
janning


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Frank D. Engel, Jr.]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

As for the first part, try this:

(SELECT file_name FROM file_info_1 WHERE file_name = '/') UNION
(SELECT file_name FROM file_info_2 WHERE file_parent_dir = '/') UNION
(SELECT file_name FROM file_info_3 WHERE file_parent_dir = '/')

As for sorting the combined results, it would be along the lines of the
following, although I suspect I am missing something here (untested, if
it doesn't work, someone else might know how to fix it for you):

SELECT file_name
FROM ((SELECT file_name FROM file_info_1 WHERE file_name = '/') UNION
(SELECT file_name FROM file_info_2 WHERE file_parent_dir
= '/') UNION
(SELECT file_name FROM file_info_3 WHERE file_parent_dir
= '/')) AS a
ORDER BY file_name


On Jan 10, 2005, at 12:22 PM, Madison Kelly wrote:

> Hi all,
>
> I have another question, I hope it isn't too basic. ^.^
>
> I want to do a select from multiple tables but not join them. What I
> am trying to do is something like this (though this doesn't work as I
> need):
>
> SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a,
> file_info_2 b, file_info_3 c WHERE a.file_name='/' AND
> b.file_parent_dir='/' AND c.file_parent_dir='/';
>
> That returns every combination of the results from the three tables
> which is a huge number. What I need is to return all of the matches in
> all of the tables in a single column. Once I have all the matches in
> one column the next trick is to sort the combined results (any tips
> there?).
>
> I hope the question was clear. Please let me know if it wasn't.
> Thanks!
>
> Madison
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>

- -----------------------------------------------------------
Frank D. Engel, Jr. <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB4r6/7aqtWrR9cZoRAgxhAJ9p1tJBs+xmlZ/TlgKVOaAC+FtCEACfa+1g
Uf8dStwt9O2hwlP56chWabk=
=a+F/
-----END PGP SIGNATURE-----



__________________________________________________ _________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at [url]www.doteasy.com[/url]


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo@postgresql.org[/email])

[Alex Turner]

To be honest, it looks like you might need a schema adjustment.
Normalization means keeping one kind of thing in one place, avoiding
ugly queries. Inheritance can also help with this too.

Alex Turner
NetEconomist


On Mon, 10 Jan 2005 12:22:41 -0500, Madison Kelly <linux@alteeve.com> wrote:

> Hi all,
>
> I have another question, I hope it isn't too basic. ^.^
>
> I want to do a select from multiple tables but not join them. What I
> am trying to do is something like this (though this doesn't work as I need):
>
> SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a,
> file_info_2 b, file_info_3 c WHERE a.file_name='/' AND
> b.file_parent_dir='/' AND c.file_parent_dir='/';
>
> That returns every combination of the results from the three tables
> which is a huge number. What I need is to return all of the matches in
> all of the tables in a single column. Once I have all the matches in one
> column the next trick is to sort the combined results (any tips there?).
>
> I hope the question was clear. Please let me know if it wasn't. Thanks!
>
> Madison
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [email]majordomo@postgresql.org[/email]

[Michael Fuhr]

On Mon, Jan 10, 2005 at 12:22:41PM -0500, Madison Kelly wrote:

> What I need is to return all of the matches in all of the tables
> in a single column.

Maybe you're looking for UNION -- see the "Combining Queries" section
in the "Queries" chapter of the documentation.

--
Michael Fuhr
[url]http://www.fuhr.org/~mfuhr/[/url]

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Madison Kelly]

John Sidney-Woollett wrote:

> I hope I've read your question properly - I seem to be giving answers to
> un-asked questions lately! ;)
>
> How about...
>
> SELECT file_name from file_info_1 WHERE file_name='/'
> union
> SELECT file_name from file_info_2 WHERE file_parent_name='/'
> union
> SELECT file_name from file_info_3 WHERE file_parent_name='/'
> order by file_name;
>
> Does that do what you want?
>
> John Sidney-Woollett

That worked perfectly, though now I realize I have another problem that
is hopefully easy to get around. Here is my query (with the type fixed):

SELECT file_name FROM file_info_1 WHERE file_parent_dir='/' UNION SELECT
file_name FROM file_info_2 WHERE file_parent_dir='/' UNION SELECT
file_name FROM file_info_3 WHERE file_parent_dir='/' ORDER BY file_name;

The trick now is I need to know which table each result came from. I can
add another column and record the table number and SELECT that at the
same time but before I do I was wondering if I can do this more
efficiently or elegantly.

Thank you very much!

Madison

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo@postgresql.org[/email])

[Alvaro Herrera]

On Mon, Jan 10, 2005 at 12:47:53PM -0500, Madison Kelly wrote:

Madison,

> The trick now is I need to know which table each result came from. I can
> add another column and record the table number and SELECT that at the
> same time but before I do I was wondering if I can do this more
> efficiently or elegantly.

You can get the Oid of the table very easily with the "tableoid" column.
If you want the name you can use tableoid::regclass, though I'm not sure
if that works as a normal text column.

Be sure to grok the difference between UNION and UNION ALL (which is the
fact that UNION sorts and "uniqs" its input).

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
Oh, oh, las chicas galacianas, lo harán por las perlas,
¡Y las de Arrakis por el agua! Pero si buscas damas
Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

[url]http://archives.postgresql.org[/url]

[Madison Kelly]

Alex Turner wrote:

> To be honest, it looks like you might need a schema adjustment.
> Normalization means keeping one kind of thing in one place, avoiding
> ugly queries. Inheritance can also help with this too.
>
> Alex Turner
> NetEconomist

Hi,

The schema started off with all the data in one place as you
described but in this case I kind of had to split out the data into
different tables in order to win pretty major performance gains
elsewhere. By Inheritance you mean using pkeys and such, right? I
haven't looked into using pkeys and such yet... I haven't seen a
compelling reason to in my app yet, this may be it?

Thanks for your reply!

Madison

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Madison Kelly]

Bruno Wolff III wrote:

> On Mon, Jan 10, 2005 at 12:47:53 -0500,
> Madison Kelly <linux@alteeve.com> wrote:
>

>>The trick now is I need to know which table each result came from. I can
>>add another column and record the table number and SELECT that at the
>>same time but before I do I was wondering if I can do this more
>>efficiently or elegantly.

>
>
> You can add a constant to the select list in each of the subselects
> that indicates which table is being used.

^.^; Can you point me to docs that will help me learn how to do that?
Thanks! Or rather, do you mean add a column to the table with an ID for
the table that I select beside the file_name? If so, that is what I am
planning to do if I can't find a smoother way to do it.

Thanks!!

Madison

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

[url]http://archives.postgresql.org[/url]

[Bruno Wolff III]

On Mon, Jan 10, 2005 at 12:47:53 -0500,
Madison Kelly <linux@alteeve.com> wrote:

>
> The trick now is I need to know which table each result came from. I can
> add another column and record the table number and SELECT that at the
> same time but before I do I was wondering if I can do this more
> efficiently or elegantly.

You can add a constant to the select list in each of the subselects
that indicates which table is being used.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Derik Barclay]

SELECT 'table1' AS source, file_name FROM file_info_1 WHERE
file_parent_dir='/'
UNION
SELECT 'table2' AS source, file_name FROM file_info_2 WHERE
file_parent_dir='/'
UNION
SELECT 'table3' AS source, file_name FROM file_info_3 WHERE
file_parent_dir='/' ORDER BY file_name;

On January 10, 2005 01:24 pm, Madison Kelly wrote:

> Bruno Wolff III wrote:

> > On Mon, Jan 10, 2005 at 12:47:53 -0500,
> >
> > Madison Kelly <linux@alteeve.com> wrote:

> >>The trick now is I need to know which table each result came from. I can
> >>add another column and record the table number and SELECT that at the
> >>same time but before I do I was wondering if I can do this more
> >>efficiently or elegantly.

> >
> > You can add a constant to the select list in each of the subselects
> > that indicates which table is being used.

>
> ^.^; Can you point me to docs that will help me learn how to do that?
> Thanks! Or rather, do you mean add a column to the table with an ID for
> the table that I select beside the file_name? If so, that is what I am
> planning to do if I can't find a smoother way to do it.
>
> Thanks!!
>
> Madison
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> [url]http://archives.postgresql.org[/url]

--
Givex - [url]http://www.givex.com/[/url]
Derik Barclay <dbarclay@givex.com>, Systems Software Engineer
+1 416 350 9660
+1 416 250 9661 (fax)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Frank D. Engel, Jr.]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT file_name, 1 FROM file_info_1 WHERE file_parent_dir='/' UNION
SELECT file_name, 2 FROM file_info_2 WHERE file_parent_dir='/' UNION
SELECT file_name, 3 FROM file_info_3 WHERE file_parent_dir='/'
ORDER BY file_name;

The second column now shows which table it came from. No need to mess
with adding fields, OIDs, etc...

On Jan 10, 2005, at 1:24 PM, Madison Kelly wrote:

> Bruno Wolff III wrote:

>> On Mon, Jan 10, 2005 at 12:47:53 -0500,
>> Madison Kelly <linux@alteeve.com> wrote:

>>> The trick now is I need to know which table each result came from. I
>>> can add another column and record the table number and SELECT that
>>> at the same time but before I do I was wondering if I can do this
>>> more efficiently or elegantly.

>> You can add a constant to the select list in each of the subselects
>> that indicates which table is being used.

>
> ^.^; Can you point me to docs that will help me learn how to do that?
> Thanks! Or rather, do you mean add a column to the table with an ID
> for the table that I select beside the file_name? If so, that is what
> I am planning to do if I can't find a smoother way to do it.
>
> Thanks!!
>
> Madison
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> [url]http://archives.postgresql.org[/url]
>
>

- -----------------------------------------------------------
Frank D. Engel, Jr. <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB4s/o7aqtWrR9cZoRAuYtAJ95XxbFAcfK5MBDU+sC4ktulxqfwwCfb IfM
mjQKofx230j5myapOSbGCAc=
=WEWW
-----END PGP SIGNATURE-----



__________________________________________________ _________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at [url]www.doteasy.com[/url]


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Bruno Wolff III]

On Mon, Jan 10, 2005 at 13:24:50 -0500,
Madison Kelly <linux@alteeve.com> wrote:

>
> ^.^; Can you point me to docs that will help me learn how to do that?
> Thanks! Or rather, do you mean add a column to the table with an ID for
> the table that I select beside the file_name? If so, that is what I am
> planning to do if I can't find a smoother way to do it.

You can just add a list item. Either a number or a quoted string.
Something like:
SELECT 'Table A', col1, col2, col3 FROM tablea;

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Madison Kelly]

Frank D. Engel, Jr. wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> SELECT file_name, 1 FROM file_info_1 WHERE file_parent_dir='/' UNION
> SELECT file_name, 2 FROM file_info_2 WHERE file_parent_dir='/' UNION
> SELECT file_name, 3 FROM file_info_3 WHERE file_parent_dir='/'
> ORDER BY file_name;
>
> The second column now shows which table it came from. No need to mess
> with adding fields, OIDs, etc...

Thank you Frank, Bruno and Derik,

That worked wonderfully! Derik's suggestion also worked perfectly,
too. I would imagine this method is slightly faster that his because I
am not really assigning anything? I guess I will need to run some
benchmarks. It is really nice to have options though.

Thank you all again! The amount of time you guys have saved me since
I joined is just phenominal!

Madison


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo@postgresql.org[/email])