Select Notes & Comments - Efficiency Question

What's the most efficient way to do this?

[jd01]

Hey,

I usually use flat file DB's, but have the need to relate notes to comments and this is a bit out of my MySQL knowledge spectrum for efficiency... I can make it work, but wonder what some people with more experience doing these type of selects think and if there are efficiency gains, so here goes:

1.) Parameters:
SELECT * Notes Info with a limit of 10, with the 10 being the most recent.
SELECT * Comments related by Note ID.

I know there are other ways I can do this, but I need to select all 10 Notes, with or without comments and want to only select comments that correspond to the notes selected...

2.) What I have considered.

In long hand using 2 SELECTs & PHP it looks something like: (I'm making this up as I go right now, so don't scrutinize my PHP too much, just go with the point. Thanks! I might have an error or two and there's a bit missing ;))

$Query="SELECT * FROM Notes WHERE UserID='".$UserID."' ORDER BY Time DESC LIMIT 10";

$noteIDs=array();
while($Result=mysql_fetch_array($Query)) {
$noteIDs[]=$Result['id'];
}

if(count($noteIDs)>1) {
$idsToGet=implode(" OR ",$noteIDs);
}

else { $idsToGet=$noteIDs[0]; }

$selComments="SELECT * FROM Comments WHERE noteID='".$idsToGet."' ORDER BY id";

Is there a more efficient way to do this with a single SELECT?
TIA for any advice...

EDITED: Couple of minor corrections.

[rocknbil]

I think you want some sort of a join, in which you join on the notes id. Like

$Query='select Notes.*,Comments.* from Notes,Comments' .
' where Notes.id=Comments.noteID and'
' Notes.UserID=' . $UserID .
' order by Notes.Time desc limit 10';

This will give you an array first of all Notes fields followed by all Comments fields. Note that numeric data types do not need to be quoted in a select - it does not hurt to quote them, but not necessary. If UserID is text, add quotes.

As for efficiency, unless you need **all** fields, select only what you want:

$Query='select Notes.UserID,Notes.UserName,Notes.Time,Comments.UserComment from Notes,Comments' .
' where Notes.id=Comments.noteID and'
' Notes.UserID=' . $UserID .
' order by Notes.Time desc limit 10';

[jd01]

Thanks rocknbill,

I'll play around with it a bit... I had thought of using a join, but it's not my forte. I appreciate the suggestion! Thanks.