Professional Web Applications Themes

How to Query on multiple tables - ASP Database

Hi, I'm new at this and having a problem on doing a query for multiple tables. Note: all tables have the same fields as well. Below is a what I tried. ---------------------------------------------- Error: The specified field 'call_back' could refer to more than one table listed in the FROM clause of your SQL statement. SQL : select * from texas, florida, Iowa, Virginia where call_back=true ----------------------------------------------- How can I do this correctly? Thanks, jt...

  1. #1

    Default How to Query on multiple tables

    Hi,

    I'm new at this and having a problem on doing a query for multiple tables.

    Note: all tables have the same fields as well. Below is a what I tried.

    ----------------------------------------------
    Error: The specified field 'call_back' could refer to more than one table
    listed in the FROM clause of your SQL statement.

    SQL : select * from texas, florida, Iowa, Virginia where call_back=true
    -----------------------------------------------

    How can I do this correctly?

    Thanks,
    jt


    jt Guest

  2. #2

    Default Re: How to Query on multiple tables

    jt wrote:
    > Hi,
    >
    > I'm new at this and having a problem on doing a query for multiple
    > tables.
    Never ask a question about how to write a query (or anything else
    database-related for that matter) without telling us the type and version of
    database you are using. It is _always_ relevant! :-)
    >
    > Note: all tables have the same fields as well. Below is a what I
    > tried.
    >
    > ----------------------------------------------
    > Error: The specified field 'call_back' could refer to more than one
    > table listed in the FROM clause of your SQL statement.
    >
    > SQL : select * from texas, florida, Iowa, Virginia where
    > call_back=true -----------------------------------------------
    >
    There is no way to soften this. This is a horrible database design. You
    should have a single table with an extra column for the state names. Run the
    following union query so you will see what your table should look like:

    SELECT 'Texas' State, t.* From texas t
    UNION ALL
    SELECT 'Florida', t.* From florida t

    UNION ALL
    SELECT 'Iowa', t.* From Iowa t

    UNION ALL
    SELECT 'Virginia' , t.* From Virginia t

    Redesign your database so the data looks like the results of the above
    query. Then it's simply a matter of (assuming you call the new table
    StateInfo):

    SELECT <column list> FROM StateInfo WHERE call_back=true

    Note: I deliberately used <column list>. Do not be lazy and use selstar
    (Select *) in production code. It effects performance due to the need for
    ADO to make two trips to the database instead of one.

    HTH,
    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Barrows [MVP] Guest

  3. #3

    Default Re: How to Query on multiple tables


    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:uPbGV5sNEHA.3348TK2MSFTNGP09.phx.gbl...
    > jt wrote:
    > > Hi,
    > >
    > > I'm new at this and having a problem on doing a query for multiple
    > > tables.
    >
    > Never ask a question about how to write a query (or anything else
    > database-related for that matter) without telling us the type and version
    of
    > database you are using. It is _always_ relevant! :-)
    >
    > >
    > > Note: all tables have the same fields as well. Below is a what I
    > > tried.
    > >
    > > ----------------------------------------------
    > > Error: The specified field 'call_back' could refer to more than one
    > > table listed in the FROM clause of your SQL statement.
    > >
    > > SQL : select * from texas, florida, Iowa, Virginia where
    > > call_back=true -----------------------------------------------
    > >
    >
    > There is no way to soften this. This is a horrible database design. You
    > should have a single table with an extra column for the state names. Run
    the
    > following union query so you will see what your table should look like:
    >
    > SELECT 'Texas' State, t.* From texas t
    > UNION ALL
    > SELECT 'Florida', t.* From florida t
    >
    > UNION ALL
    > SELECT 'Iowa', t.* From Iowa t
    >
    > UNION ALL
    > SELECT 'Virginia' , t.* From Virginia t
    >
    > Redesign your database so the data looks like the results of the above
    > query. Then it's simply a matter of (assuming you call the new table
    > StateInfo):
    >
    > SELECT <column list> FROM StateInfo WHERE call_back=true
    >
    > Note: I deliberately used <column list>. Do not be lazy and use selstar
    > (Select *) in production code. It effects performance due to the need for
    > ADO to make two trips to the database instead of one.
    >
    > HTH,
    > Bob Barrows
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    >
    Sorry about not including the what database. I am using MS Access 2000.

    I will try running the statements above.

    Thanks,
    jt


    jt Guest

  4. #4

    Default Re: How to Query on multiple tables

    jt wrote:
    >>
    >> SELECT 'Texas' State, t.* From texas t
    >> UNION ALL
    >> SELECT 'Florida', t.* From florida t
    >>
    >> UNION ALL
    >> SELECT 'Iowa', t.* From Iowa t
    >>
    >> UNION ALL
    >> SELECT 'Virginia' , t.* From Virginia t
    >>
    >
    > I will try running the statements above.
    >

    Just to make sure: that is a single query (I accidentally put the blank
    lines in there). You will need to type or paste it all into the SQL View of
    an Access query builder window.

    SELECT 'Texas' State, t.* From texas t
    UNION ALL
    SELECT 'Florida', t.* From florida t
    UNION ALL
    SELECT 'Iowa', t.* From Iowa t
    UNION ALL
    SELECT 'Virginia' , t.* From Virginia t

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows [MVP] Guest

Similar Threads

  1. update multiple records in multiple tables from one form
    By Anj01 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 17th, 05:54 AM
  2. QUERY two tables from different databases
    By WhozitsPop in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 25th, 03:15 PM
  3. Query 5 Tables
    By Speegs in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 18th, 08:11 PM
  4. SQL query with 3 tables.
    By Craig Mason in forum PHP Development
    Replies: 0
    Last Post: May 19th, 01:33 AM
  5. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 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