Professional Web Applications Themes

SELECT from multiple tables (not join though) - PostgreSQL / PGSQL

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

  1. #1

    Default 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

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

    Madison Kelly Guest

  2. #2

    Default Re: 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:
    > 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]majordomopostgresql.org[/email])

    Madison Kelly Guest

  3. #3

    Default Re: SELECT from multiple tables (not join though)

    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]majordomopostgresql.org[/email]

    John Sidney-Woollett Guest

  4. #4

    Default Re: SELECT from multiple tables (not join though)

    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 doentation visit

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

    or your local postgresql doentation.

    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

    Janning Vygen Guest

  5. #5

    Default Re: SELECT from multiple tables (not join though)

    -----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. <fde101fjrhome.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]majordomopostgresql.org[/email])

    Frank D. Engel, Jr. Guest

  6. #6

    Default Re: SELECT from multiple tables (not join though)

    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 <linuxalteeve.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]majordomopostgresql.org[/email]

    Alex Turner Guest

  7. #7

    Default Re: SELECT from multiple tables (not join though)

    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 doentation.

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

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

    Michael Fuhr Guest

  8. #8

    Default Re: SELECT from multiple tables (not join though)

    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]majordomopostgresql.org[/email])

    Madison Kelly Guest

  9. #9

    Default Re: SELECT from multiple tables (not join though)

    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]

    Alvaro Herrera Guest

  10. #10

    Default Re: SELECT from multiple tables (not join though)

    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 yze is your friend

    Madison Kelly Guest

  11. #11

    Default Re: SELECT from multiple tables (not join though)

    Bruno Wolff III wrote:
    > On Mon, Jan 10, 2005 at 12:47:53 -0500,
    > Madison Kelly <linuxalteeve.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]

    Madison Kelly Guest

  12. #12

    Default Re: SELECT from multiple tables (not join though)

    On Mon, Jan 10, 2005 at 12:47:53 -0500,
    Madison Kelly <linuxalteeve.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

    Bruno Wolff III Guest

  13. #13

    Default Re: SELECT from multiple tables (not join though)

    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 <linuxalteeve.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 <dbarclaygivex.com>, Systems Software Engineer
    +1 416 350 9660
    +1 416 250 9661 (fax)

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

    Derik Barclay Guest

  14. #14

    Default Re: SELECT from multiple tables (not join though)

    -----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 <linuxalteeve.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. <fde101fjrhome.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 yze is your friend

    Frank D. Engel, Jr. Guest

  15. #15

    Default Re: SELECT from multiple tables (not join though)

    On Mon, Jan 10, 2005 at 13:24:50 -0500,
    Madison Kelly <linuxalteeve.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

    Bruno Wolff III Guest

  16. #16

    Default Re: SELECT from multiple tables (not join though)

    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]majordomopostgresql.org[/email])

    Madison Kelly Guest

Similar Threads

  1. Listing join from tables...
    By createmedia in forum Coldfusion Database Access
    Replies: 5
    Last Post: June 5th, 09:02 PM
  2. Select, Join & field values - 2 tables
    By Justin Koivisto in forum MySQL
    Replies: 3
    Last Post: February 13th, 08:06 PM
  3. New to Joines - Inner Join on 4 Tables
    By FusionRed in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: June 14th, 03:14 PM
  4. HELP.. Recordset JOIN tables
    By Addy_31 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 4th, 01:51 AM
  5. trying to update a table after making a join select query on two tables
    By rob merritt in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: March 1st, 10:33 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139