sql query question

kpachopoulos · 09-07-2007, 12:12 PM

Hi,
is there a way i can feed the output of an sql query to another query? I don't mind if it is standard SQL or MySQL specific. For example,

Code:

SELECT X=CommiterID FROM Commiter WHERE commiterName="NEO";
save the resulting commiter ID (to a variable maybe X maybe?) and feed it to:
SELECT * FROM File WHERE commiterID=X;

Hobbletoe · 09-07-2007, 12:20 PM

Slap it all together ...

Code:

SELECT a.*
  FROM file a,
       commiter b
 WHERE a.CommiterID = b.CommiterID
   AND b.CommiterName = 'NEO';

a and b are short hand for the table names. Everywhere you see b.field, you could put commiter.field. Just easier and shorter this way.

PTrenholme · 09-07-2007, 02:39 PM

Or use the IN operator:

Code:

SELECT * FROM File
  WHERE commiterID IN
 (SELECT CommiterID FROM Commiter WHERE commiterName="NEO";);

[edit]
Oh, note that proper table indexing can make such queries fairly efficient. Without proper indexes these kinds of queries can take hours to finish.
[/edit]

jlinkels · 09-08-2007, 08:46 PM

Remember that using a select statement in a select statement is only possibly beyond a certain version of MySQL (I believe 4.1). I say this because on Debian Sarge you'd still be running 4.0.

PTrenholme: You seem to know what "proper indexing is". I want to lean better MySQL design. Do you have a pointer where I can find such information. No, Google does not tell. A paper version book is OK as well.

Thanks
jlinkels

PTrenholme · 09-09-2007, 10:14 AM

First, sorry: I haven't looked at data base books for years, and have no idea what's current.

Here's my take on indexing:

Basically, most data base systems permit you to define multiple indexes on a table, and, again, most of them will return a value from the index if the query can be satisfied using data in the index, without looking at the actual table.

For example, in the query I suggested in answer to your question:

Code:

SELECT * FROM File
  WHERE commiterID IN
 (SELECT commiterID FROM Commiter WHERE commiterName="NEO");

you are first looking for the "commiterID" for a specific "commiterName" and then looking for all the information stored in "File" for that "commiterID". Thus the first table ("Commiter" should be indexed jointly on both "commiterName" and "commiterID" in that order so a hit on "commiterName" in the table will return "commiterID" at the same time. (Note that, if these are all the fields in the table, the whole table will be physically stored in the index by most data base systems.)

Obviously that second table ("File") needs to be indexed on "commiterID." Depending on the data in that table, that should probably be the primary key on the table.

So the bottom line on indexing is just use common sense, and consider carefully the use to which the data will be put.

[aside]
You should note that the query as I wrote it will return all the data for all entries where the "commiterName" in the "Commiter" table is matched. Thus, unless the "commiterName" is restricted to be unique in your data base design, you may get multiple records returned. If "commiterName" is unique, then "commiterID" is redundant, and you would be better off just using "commiterName" as your primary key on the "file" table.
[/asside]

jlinkels · 09-10-2007, 12:54 PM

Thanks. Let ne see and try what I can do with this knowledge.

jlinkels