Re: Really simple SQL problem

On Thursday 22 May 2003 12:38 in
<a6aa2e0d(dot)0305220338(dot)30aab9f(at)posting(dot)google(dot)com>, Dan
(dan_nash(at)hotmail(dot)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(at)spamtrap(dot)ntlworld(dot)com (David W Noon)
Remove spam trap to reply via e-mail.
======================================================