Обсуждение: SELECT from multiple tables (not join though)
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
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 >
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
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, 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 >
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
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.
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
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)
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;
-----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
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