Professional Web Applications Themes

multiple lookups in one query - MySQL

Hi, I've got two tables, one of which is a lookup for the other. It works something like this: table jobs: ----------- id title manager designer ________________________________ 1 dbproj 1 3 2 email 2 5 table people: ------------- id name ____________ 1 fred 2 nancy 3 joe 4 ted 5 marge I would like to be able to do a query like "SELECT * FROM jobs" and substitute the names from the people table on the fly. I can do this for one person using a join, like "SELECT * FROM jobs WHERE jobs.manager = people.id" but I can't figure ...

  1. #1

    Default multiple lookups in one query

    Hi,

    I've got two tables, one of which is a lookup for the other. It works
    something like this:

    table jobs:
    -----------
    id title manager designer
    ________________________________

    1 dbproj 1 3
    2 email 2 5


    table people:
    -------------
    id name
    ____________

    1 fred
    2 nancy
    3 joe
    4 ted
    5 marge


    I would like to be able to do a query like "SELECT * FROM jobs" and
    substitute the names from the people table on the fly. I can do this for
    one person using a join, like "SELECT * FROM jobs WHERE jobs.manager =
    people.id" but I can't figure out how to do it with more than one (and
    in my application I actually have four).

    My current method involves making five separate selects and simply
    swapping values in a hash. Kinda dumb I think.

    I can't use subselects without telling the IT folks to upgrade mysql,
    which they aren't going to want to do. Any ideas?
    Wild Bill Hiccup Guest

  2. #2

    Default Re: multiple lookups in one query

    "Wild Bill Hiccup" <nerdlingeranimalhouse.net> wrote in message
    news:duk3hd$mk4$1news-int2.gatech.edu...
    > I would like to be able to do a query like "SELECT * FROM jobs" and
    > substitute the names from the people table on the fly. I can do this for
    > one person using a join, like "SELECT * FROM jobs WHERE jobs.manager =
    > people.id" but I can't figure out how to do it with more than one (and in
    > my application I actually have four).
    You need to use the people table more than once in the query:

    SELECT ...
    FROM jobs AS j, people AS m, people AS d
    WHERE j.manager = m.id AND j.designer = d.id

    Or the alternate syntax that I prefer, that does the same thing:

    SELECT ...
    FROM jobs AS j
    INNER JOIN people AS m ON j.manager = m.id
    INNER JOIN people AS d ON j.designer = d.id

    Regards,
    Bill K.


    Bill Karwin Guest

  3. #3

    Default Re: multiple lookups in one query

    Bill Karwin wrote:
    > "Wild Bill Hiccup" <nerdlingeranimalhouse.net> wrote in message
    > news:duk3hd$mk4$1news-int2.gatech.edu...
    >> I would like to be able to do a query like "SELECT * FROM jobs" and
    >> substitute the names from the people table on the fly. I can do this for
    >> one person using a join, like "SELECT * FROM jobs WHERE jobs.manager =
    >> people.id" but I can't figure out how to do it with more than one (and in
    >> my application I actually have four).
    >
    > You need to use the people table more than once in the query:
    >
    > SELECT ...
    > FROM jobs AS j, people AS m, people AS d
    > WHERE j.manager = m.id AND j.designer = d.id
    >
    > Or the alternate syntax that I prefer, that does the same thing:
    >
    > SELECT ...
    > FROM jobs AS j
    > INNER JOIN people AS m ON j.manager = m.id
    > INNER JOIN people AS d ON j.designer = d.id
    >
    > Regards,
    > Bill K.
    >
    >
    Ah. Makes good sense. Now why isn't that in the MySQL Cookbook?

    thx.
    Wild Bill Hiccup Guest

  4. #4

    Default Re: multiple lookups in one query

    Peter H. Coffin wrote:
    > On Tue, 07 Mar 2006 15:01:35 -0500, Wild Bill Hiccup wrote:
    >> Ah. Makes good sense. Now why isn't that in the MySQL Cookbook?
    >
    > Mostly because it's pretty standard SQL and nothing exciting about
    > MySQL.
    >
    Apparently you haven't looked at the MySQL Cookbook. Consider this, on
    p. 151:

    Problem: You want to display some or all of the columns from a table.
    Solution: Use * as a shortcut that selects all columns. Or name the
    columns you want to see explicitly.

    Now, are you telling me that this is sufficiently non-standard and
    exciting enough that it warranted inclusion, and my problem doesn't?
    Wild Bill Hiccup Guest

  5. #5

    Default Re: multiple lookups in one query

    Wild Bill Hiccup wrote:
    > Peter H. Coffin wrote:
    >
    >> On Tue, 07 Mar 2006 15:01:35 -0500, Wild Bill Hiccup wrote:
    >>
    >>> Ah. Makes good sense. Now why isn't that in the MySQL Cookbook?
    >>
    >>
    >> Mostly because it's pretty standard SQL and nothing exciting about
    >> MySQL.
    >
    >
    > Apparently you haven't looked at the MySQL Cookbook. Consider this, on
    > p. 151:
    >
    > Problem: You want to display some or all of the columns from a table.
    > Solution: Use * as a shortcut that selects all columns. Or name the
    > columns you want to see explicitly.
    >
    > Now, are you telling me that this is sufficiently non-standard and
    > exciting enough that it warranted inclusion, and my problem doesn't?
    I think it's appropriate.

    SELECT * is a basic SQL statement - and I would expect it to be in ANY
    tutorial/book/whatever. And this is a very basic tutorial

    OTOH, what you need to do is a little more advanced. I wouldn't
    necessarily expect it to be in a basic tutorial. There's just no way
    you can cover every possible combination, you need to draw the line
    somewhere.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  6. #6

    Default Re: multiple lookups in one query

    Jerry Stuckle wrote:
    > Wild Bill Hiccup wrote:
    >> Peter H. Coffin wrote:
    >>
    >>> On Tue, 07 Mar 2006 15:01:35 -0500, Wild Bill Hiccup wrote:
    >>>
    >>>> Ah. Makes good sense. Now why isn't that in the MySQL Cookbook?
    >>>
    >>>
    >>> Mostly because it's pretty standard SQL and nothing exciting about
    >>> MySQL.
    >>
    >>
    >> Apparently you haven't looked at the MySQL Cookbook. Consider this, on
    >> p. 151:
    >>
    >> Problem: You want to display some or all of the columns from a table.
    >> Solution: Use * as a shortcut that selects all columns. Or name the
    >> columns you want to see explicitly.
    >>
    >> Now, are you telling me that this is sufficiently non-standard and
    >> exciting enough that it warranted inclusion, and my problem doesn't?
    >
    > I think it's appropriate.
    >
    > SELECT * is a basic SQL statement - and I would expect it to be in ANY
    > tutorial/book/whatever. And this is a very basic tutorial
    >
    > OTOH, what you need to do is a little more advanced. I wouldn't
    > necessarily expect it to be in a basic tutorial. There's just no way
    > you can cover every possible combination, you need to draw the line
    > somewhere.
    >
    I suppose you've got a point. On the other hand, in my admittedly
    hodgepodge approach to mysql, I never ran across the notion that you
    could make more than one alias to a table. All the examples I've seen
    only ever show one, and it never occurred to me that you could do more
    than one. Would have saved me a fair bit of grief.
    Wild Bill Hiccup Guest

  7. #7

    Default Re: multiple lookups in one query

    Wild Bill Hiccup wrote:
    >
    > I suppose you've got a point. On the other hand, in my admittedly
    > hodgepodge approach to mysql, I never ran across the notion that you
    > could make more than one alias to a table. All the examples I've seen
    > only ever show one, and it never occurred to me that you could do more
    > than one. Would have saved me a fair bit of grief.
    Agreed. It isn't something you see (or use) every day, but it's
    available. And no, you don't see it very often in the books.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  8. #8

    Default Re: multiple lookups in one query

    Peter H. Coffin wrote:
    >> Now, are you telling me that this is sufficiently non-standard and
    >> exciting enough that it warranted inclusion, and my problem doesn't?
    >
    > Nope, it's bog-standard SQL. It's not exactly selling me on the
    > usefulness of the book, when it contains what probably should be about
    > Page 10 stuff on Page 151, and doesn't have some pretty typical SQL
    > methods at all, such as was originally asked about.
    I'm not trying to sell you on the usefulness of the book, but if I were,
    I'd point out that that is page 151 of a 1000 page book and that pages
    1-150 cover the mysql client and connecting to a db. Man, do you have a
    chip on your shoulder or something? I can't believe you've put this much
    time into answering what was essentially a rhetorical question.

    Wild Bill Hiccup Guest

Similar Threads

  1. Query using multiple databases?
    By Gary1 in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: July 4th, 11:44 AM
  2. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  3. How to Query on multiple tables
    By jt in forum ASP Database
    Replies: 3
    Last Post: May 10th, 10:08 PM
  4. multiple db query
    By Aris Santillan in forum PHP Development
    Replies: 2
    Last Post: September 2nd, 11:07 AM
  5. Lookups
    By Adam in forum Microsoft Access
    Replies: 2
    Last Post: July 22nd, 02:21 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