From: David W Noon <dwnoon@spamtrap.ntlworld.com>
To: pgsql-sql@postgresql.org
Subject: Re: Really simple SQL problem
Date: Thu, 22 May 2003 14:54:12 +0100
Message-ID: <5d1vp-3k7.ln1@my-pc.ntlworld.com> (raw)
References: <a6aa2e0d.0305220338.30aab9f@posting.google.com>
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.
======================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dwnoon@spamtrap.ntlworld.com
Subject: Re: Really simple SQL problem
In-Reply-To: <5d1vp-3k7.ln1@my-pc.ntlworld.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox