Message-ID: <5d1vp-3k7.ln1@my-pc.ntlworld.com>
From: David W Noon <dwnoon@spamtrap.ntlworld.com>
Subject: Re: Really simple SQL problem
References: <a6aa2e0d.0305220338.30aab9f@posting.google.com>
Lines: 54
User-Agent: KNode/0.7.2
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
Date: Thu, 22 May 2003 14:54:12 +0100
Organization: ntl Cablemodem News Service
To: pgsql-sql@postgresql.org

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