Re: Really simple SQL problem

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.
======================================================