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" <nerdlinger@animalhouse.net> wrote in message
news:duk3hd$mk4$1@news-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 wrote:
> "Wild Bill Hiccup" <nerdlinger@animalhouse.net> wrote in message
> news:duk3hd$mk4$1@news-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.

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 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.
jstucklex@attglobal.net
==================

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 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.
jstucklex@attglobal.net
==================

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.