On Thursday 22 May 2003 12:38 in
<a6aa2e0d.0305220338.30aab9f@posting.google.com>, Dan
(dan_nash@hotmail.com) wrote:
> I have two tables:
>
> MARKS: MARKED:
> markid|comment MARKID | STUDENTID
> ============== ==================
> | |
>
> And I am trying to select all the comments (could be many) where the
> mark ID matches a student ID.
I assume the columns ending in "id" are of some integral data type. You
didn't tell us. Moreover, the letter cases of the column names differ; I
will assume that you did not use case-sensitive syntax to create the
tables.
> This is what I think should work. But it doesen't seem to be doing
> what I want.
>
> $Studentid = 1
>
> SELECT marks.comment FROM marks, marked WHERE
> marked.studentid=$studentid AND marked.markid=marks.markid";
^
The trailing quote mark will cause a problem in pure SQL. I presume it is an
artefact of some Perl code. Also, you seem to have no regard for letter
case, which could bite you big time in Perl.
> Will someone please tell me what I am doing wrong and how to fix this.
A more modern approach, which totally avoids the possibility of a Cartesian
product, would look more like this:
SELECT a.comment FROM marks AS a INNER JOIN marked AS b ON a.markid = b.markid WHERE b.studentid = 1;
Since you seem to be using Perl, you can wrap the above in quotes as a
single line of text and replace the constant 1 with an interpolated
variable: $Studentid or $studentid as the mood takes you.
--
Regards,
Dave [RLU#314465]
======================================================
dwnoon@spamtrap.ntlworld.com (David W Noon)
Remove spam trap to reply via e-mail.
======================================================
