SELECT from multiple tables (not join though)

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:

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:

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

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

or your local postgresql documentation.

kind regards,
janning

-----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 www.doteasy.com

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, 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
http://www.fuhr.org/~mfuhr/

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

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)

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

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

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.

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?

http://archives.postgresql.org

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

-----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?

http://archives.postgresql.org

- -----------------------------------------------------------
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+sC4ktulxqfwwCfbIfM
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 www.doteasy.com

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;

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