in reply to Re^2: Avoiding SQL double jeopardy
in thread Avoiding SQL double jeopardy

I'm not sure what you're trying to do here, but whatever it is, this is overcomplicating it.

Aside from that, you're using dynamic SQL and a flag to determine if the person is a teacher or not. Use a Role id instead. That's what you want. The flag is a trick, and, it may bite you in the future.

Can more than one person be in the same session? Why does it matter if the person is a teacher or student?

It looks like you're inserting each person twice, once as a teacher and once as a student. If so, it can be done in one query:

INSERT INTO SessPeople (SessId, PersonId, IsTeacher) SELECT ?, ? 0 UNION ALL SELECT ?, ? 1;

The second query is daunting. I have no idea what you're trying to do. If the original query (topic starting post) table was used, you can check via many ways. The easiest is to connect the two columns via UNION ALL:

WITH Session2 AS(SELECT AS (SELECT SessId, TeacherId, DoneDate, Topic FROM Sessions UNION ALL SELECT SessId, LearnerId, DoneDate, Topic FROM Sessions) SELECT SessId, TeacherId, DoneDate, Topic FROM Sessions2 WHERE TeacherId IN ();

The rewritten query here might be more complicated, but it does't look right. Could you explain a little more clearly what you;re trying to achieve? I have a feeling the solution is considerably simpler.

Replies are listed 'Best First'.
Re^4: Avoiding SQL double jeopardy
by GrandFather (Saint) on Jun 28, 2015 at 05:08 UTC

    The big picture is we have a weekly "experience exchange" in our development team. Each week we offer a topic we are prepared to teach for 3/4 of an hour. Then at the appointed time we pair up as teacher/learner for our weekly session. Over time it is expected that we spend about as many sessions teaching as learning.

    At present the process is mediated by post it notes stuck to a wall. The script is intended to replace the post it note scrum and help ensure everyone gets a more or less equal opportunity to teach and learn.

    So, a person can only be in each session once. For any session a person may be a teacher or a learner. There will only ever be teacher and learner roles. Each session includes everyone who is available.

    The query at the heart of the issue is to find all pending session pairings an individual or group of individuals are involved in.

    Perl is the programming world's equivalent of English
[reply]

      Thank you, i think i got it now. :) Two more clarifications, though: Can there be more than one teacher per session? Can there be more than one student per session? That is, "by definition" as opposed to how it just happens to work out.

[reply]

        Hmmm there is a little confusion of nomenclature (my fault). Properly speaking we have one XR (eXperience Replication) session a week. People pair up as a teacher and learner. If there is an odd number of people one "pair" will have one teacher and two learners.

        The current Sessions table identifies sessions by DrawDate and contains a "SessionId" which I should rename to PairId. There will be multiple pairs per weekly session.

        PairId is unique in the Sessions table but there will be multiple entries (generally two) in the SessPeople table (which I should rename to the Pairs table) for each PairId.

        Perl is the programming world's equivalent of English
[reply]

In Section Seekers of Perl Wisdom