Multiple lookups in one query
10/01/2019 11:47
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 32 email 2 5 table people: -------------id name____________ 1 fred2 nancy3 joe4 ted5 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?

Source is Usenet: comp.databases.mysql
Sign in to add a comment

Answer score: 5
10/01/2019 11:47 - 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?

Source is Usenet: comp.databases.mysql
Sign in to add a comment

Answer score: 5
10/01/2019 11:47 - Wild Bill Hiccup <nerdlinger@animalhouse.net> wrote in message news:duk3hd$mk4$1@news-int2.gatech.edu...

You need to use the people table more than once in the query: SELECT ...

FROM jobs AS j, people AS m, people AS dWHERE 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.

Source is Usenet: comp.databases.mysql
Sign in to add a comment

Answer score: 5
10/01/2019 11:47 - 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.


Source is Usenet: comp.databases.mysql
Sign in to add a comment

Answer score: 5
10/01/2019 11:47 - Ah. Makes good sense. Now why isn't that in the MySQL Cookbook? thx.


Source is Usenet: comp.databases.mysql
Sign in to add a comment

Answer score: 5
10/01/2019 11:47 - 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.


Source is Usenet: comp.databases.mysql
Sign in to add a comment

Answer score: 5
10/01/2019 11:47 - 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.


Source is Usenet: comp.databases.mysql
Sign in to add a comment

Answer score: 5
10/01/2019 11:47 - Mostly because it's pretty standard SQL and nothing exciting aboutMySQL.

Source is Usenet: comp.databases.mysql
Sign in to add a comment

eDiscover
Helpforce eDiscover provides technical articles updated each dayHelpforce eDiscover RSS feed contains the latest technical articles in RSS
Click the logo to go back to the main page
Search eDiscover
  
Categories

Click an icon to go to that category

Helpforce eDiscover contains articles about Microsoft Windows Helpforce eDiscover contains articles about Apple products and MacOS Helpforce eDiscover contains articles about Linux and POSIX operating systems Helpforce eDiscover contains articles about Helpforce Helpforce has a large variety of technical information and articles for you to read Helpforce eDiscover contains articles about databases, MYSQL, SQL Server Oracle Helpforce eDiscover contains articles about Java, JVM and the JRE Helpforce eDiscover contains articles about the QNX operating system Helpforce eDiscover contains articles about Oracle Solaris and Open Solaris Helpforce eDiscover contains articles about RISC OS, Acorn and the BBC Micro Helpforce eDiscover contains articles about Amiga and AmigaOS

Type your comment into the box below