[nycphp-talk] SQL statement question
Bill Patterson
patterson at computer.org
Sun May 23 18:04:43 EDT 2004
*mysql> select a.Student_FID from TMPhistory a, TMPhistory b
-> where a.Student_FID = b.Student_FID
-> and a.Course_FID = 5 and b.Course_FID = 2;
+-------------+
| Student_FID |
+-------------+
| 34 |
+-------------+*
to find out about 3 courses just add another alias for your table
Bill
harvey wrote:
> Hello,
>
> I'm going to create a MySql table that looks something like the
> following (I think). It's a history of courses taken by students.
>
> History_ID Student_FID Course_FID
> 1 34 2
> 2 17 7
> 3 21 5
> 4 02 5
> 5 34 5
> 6 17 4
> ... ... ...
>
> I'd like to be able to say which students have met the requirements of
> certain programs. So, I need a statement that will produce a list of
> Student_FID's that are matched with a particular set of Course_FID's.
> For instance, which students took both course 5 and course 2?
>
> So, I'm trying subqueries to find students who have taken courses
> 1,2,and 3:
>
> "SELECT *
> FROM
> (SELECT *
> FROM
> (SELECT *
> FROM history
> WHERE course_fid = 3)
> AS id3
> WHERE course_fid = 2)
> AS id2
> WHERE course_fid = 1"
>
> I get an error that my sql syntax is wrong. Maybe it is. Or maybe my
> host's version of MySQL is too old? Is there a better SQL statement?
> Any help is appreciated...
>
> Thanks!
>
>------------------------------------------------------------------------
>
>_______________________________________________
>talk mailing list
>talk at lists.nyphp.org
>http://lists.nyphp.org/mailman/listinfo/talk
>
>
>From hans not junk at nyphp.com Sun May 23 18:59:05 2004
Return-Path: <hans not junk at nyphp.com>
Received: from ehost011-1.intermedia.net (ehost011-1.intermedia.net
[64.78.21.3]) by virtu.nyphp.org (Postfix) with ESMTP id 10B55A85EA
for <talk at lists.nyphp.org>; Sun, 23 May 2004 18:59:05 -0400 (EDT)
X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: RE: [nycphp-talk] SQL statement question
Date: Sun, 23 May 2004 15:59:01 -0700
Message-ID: <41EE526EC2D3C74286415780D3BA9F870221406B at ehost011-1.exch011.intermedia.net>
X-MS-Has-Attach:
X-MS-TNEF-Correlator:
Thread-Topic: [nycphp-talk] SQL statement question
Thread-Index: AcRBDovpUA2oBRwbTISP1/4/K1jgUQAAjh9g
From: "Hans Zaunere" <hans not junk at nyphp.com>
To: "NYPHP Talk" <talk at lists.nyphp.org>
X-BeenThere: talk at lists.nyphp.org
X-Mailman-Version: 2.1.4
Precedence: list
Reply-To: NYPHP Talk <talk at lists.nyphp.org>
List-Id: NYPHP Talk <talk.lists.nyphp.org>
List-Unsubscribe: <http://lists.nyphp.org/mailman/listinfo/talk>,
<mailto:talk-request at lists.nyphp.org?subject=unsubscribe>
List-Archive: <http://lists.nyphp.org/pipermail/talk>
List-Post: <mailto:talk at lists.nyphp.org>
List-Help: <mailto:talk-request at lists.nyphp.org?subject=help>
List-Subscribe: <http://lists.nyphp.org/mailman/listinfo/talk>,
<mailto:talk-request at lists.nyphp.org?subject=subscribe>
X-List-Received-Date: Sun, 23 May 2004 22:59:05 -0000
> Hello,
> =20
> I'm going to create a MySql table that looks something like=20
> the following (I think). It's a history of courses taken by students.
> =20
> History_ID Student_FID Course_FID
> 1 34 2
> 2 17 7
> 3 21 5
> 4 02 5
> 5 34 5
> 6 17 4
> ... ... ...
> =20
> I'd like to be able to say which students have met the=20
> requirements of certain programs. So, I need a statement that=20
> will produce a list of Student_FID's that are matched with a=20
> particular set of Course_FID's. For instance, which students=20
> took both course 5 and course 2?=20
> =20
> So, I'm trying subqueries to find students who have taken=20
> courses 1,2,and 3:
> =20
> "SELECT *
> FROM
> (SELECT *
> FROM
> (SELECT *
> FROM history
> WHERE course_fid =3D 3)
> AS id3
> WHERE course_fid =3D 2)
> AS id2
> WHERE course_fid =3D 1"
> =20
> I get an error that my sql syntax is wrong. Maybe it is. Or=20
> maybe my host's version of MySQL is too old? Is there a=20
> better SQL statement? Any help is appreciated...
Unless you're using MySQL 4.1 or later (which I doubt, since it's not
production level yet) sub-queries are not supported.
To answer your question "which students took both course 5 and course
2?" I'd recommend a query like this (assuming History_FID is unique):
SELECT H1.Student_FID
FROM history H1 LEFT JOIN history H2 ON H2.History_ID=3DH1.History_ID
WHERE H1.Course_FID =3D '5' AND H2.Course_FID =3D '2'
H
>From hans not junk at nyphp.com Sun May 23 19:00:51 2004
Return-Path: <hans not junk at nyphp.com>
Received: from ehost011-1.intermedia.net (ehost011-1.intermedia.net
[64.78.21.3]) by virtu.nyphp.org (Postfix) with ESMTP id 0CEE8A85EA
for <talk at lists.nyphp.org>; Sun, 23 May 2004 19:00:51 -0400 (EDT)
X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: RE: [nycphp-talk] Output Buffering and Blank Page
Date: Sun, 23 May 2004 16:00:47 -0700
Message-ID: <41EE526EC2D3C74286415780D3BA9F870221406D at ehost011-1.exch011.intermedia.net>
X-MS-Has-Attach:
X-MS-TNEF-Correlator:
Thread-Topic: [nycphp-talk] Output Buffering and Blank Page
Thread-Index: AcQ/YsC9rAc7NbeqQNmvr5wsOvSy/QBtueEg
From: "Hans Zaunere" <hans not junk at nyphp.com>
To: "NYPHP Talk" <talk at lists.nyphp.org>
X-BeenThere: talk at lists.nyphp.org
X-Mailman-Version: 2.1.4
Precedence: list
Reply-To: NYPHP Talk <talk at lists.nyphp.org>
List-Id: NYPHP Talk <talk.lists.nyphp.org>
List-Unsubscribe: <http://lists.nyphp.org/mailman/listinfo/talk>,
<mailto:talk-request at lists.nyphp.org?subject=unsubscribe>
List-Archive: <http://lists.nyphp.org/pipermail/talk>
List-Post: <mailto:talk at lists.nyphp.org>
List-Help: <mailto:talk-request at lists.nyphp.org?subject=help>
List-Subscribe: <http://lists.nyphp.org/mailman/listinfo/talk>,
<mailto:talk-request at lists.nyphp.org?subject=subscribe>
X-List-Received-Date: Sun, 23 May 2004 23:00:51 -0000
> I have a problem that is about half solved and I am looking=20
> for some input from the group.
>=20
> The site I work for has a large member base (~1000 - 3000=20
> members online at any given time)
>=20
> We have output buffering turned on to improve performance. =20
> It seems to work great but on scripts that have large queries=20
> the user will see a blank white page until the script=20
> finishes. Sometimes this can be annoying when the script=20
> takes more then a few seconds to execute.
>=20
> In order to avoid this I have come up with a scheme that=20
> works very well but I would really appreciate some=20
> criticism/tips to improve it.
>=20
> The Scheme is comprised of three parts the loading script,=20
> the process script and the display script.
>=20
> Loading Script
> -----------------------------
> Displays a cute loading icon and forwards GETS/POSTS to an=20
> embedded image.
> When the page/image finishes loading (javascript onload) the=20
> page is forwarded on to the display script.
>=20
> Process Script (embedded image in the Loading Script)
> -----------------------------
> The process Script will do all the work and create the output=20
> that normally is shot out to the screen after waiting for the=20
> white/blank screen to go away. When the script is finished=20
> the output is stored to a session variable and the buffer=20
> cleared. Finally a one pixal png will be output so the=20
> Loading Script will know its done.
>=20
> Display Script
> -----------------------------
> The user will be directed here after the process script is=20
> done. If the output of the process script is in the session=20
> variable it is displayed and the session variable is unset. =20
> The user sees the results.
>=20
> So at this point this little hack is working great in my staging area.
> How will this scale to all of our users? Is it safe to put=20
> the html (about 1k) into the sessions for that many people at=20
> the same time? Am I going at this problem all wrong?
I'm by no means an expert is browser and JS tricks, but it sounds like a
clever way of doing it. Clever enough even for a presentation? :) It
seems to be a common issue people need to resolve.
H
More information about the talk
mailing list