constructing a sql query

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Dave

    constructing a sql query

    Hi

    I'm having trouble with some sql and don't know if my table design is
    incorrect.

    I have a table of delegate names.

    I have a table of possible questions the delegate can answer.

    I then have a table which contains many rows for each delegate, each row
    showing which question the delegate has answered.

    So for example, delegate id 10 has answered question 1 and question 7, so
    there would be two rows

    DelegateID QuestionID
    10 1
    10 7

    etc.

    How do i write a query that selects delegates that have answered question 1
    AND question 7?

    I tried "select delegateID where questionID=1 AND questionID=7, but of
    course no records were returned since no records satisfied both question
    IDs.

    Any help would be greatly appreciated.



  • que

    #2
    Re: constructing a sql query

    questionID=1 OR questionID=7

    Dave íàïèñàâ:
    Hi
    >
    I'm having trouble with some sql and don't know if my table design is
    incorrect.
    >
    I have a table of delegate names.
    >
    I have a table of possible questions the delegate can answer.
    >
    I then have a table which contains many rows for each delegate, each row
    showing which question the delegate has answered.
    >
    So for example, delegate id 10 has answered question 1 and question 7, so
    there would be two rows
    >
    DelegateID QuestionID
    10 1
    10 7
    >
    etc.
    >
    How do i write a query that selects delegates that have answered question1
    AND question 7?
    >
    I tried "select delegateID where questionID=1 AND questionID=7, but of
    course no records were returned since no records satisfied both question
    IDs.

    Any help would be greatly appreciated.

    Comment

    • Dave

      #3
      Re: constructing a sql query

      I have tried that, but that will give a delegate id if they have completed
      either question, but i only want to get the delegate id if they have
      completed BOTH questions.

      Thanks

      "que" <varyform@gmail .comwrote in message
      news:1165926468 .004067.38290@1 6g2000cwy.googl egroups.com...
      questionID=1 OR questionID=7

      Dave íàïèñàâ:
      Hi
      >
      I'm having trouble with some sql and don't know if my table design is
      incorrect.
      >
      I have a table of delegate names.
      >
      I have a table of possible questions the delegate can answer.
      >
      I then have a table which contains many rows for each delegate, each row
      showing which question the delegate has answered.
      >
      So for example, delegate id 10 has answered question 1 and question 7, so
      there would be two rows
      >
      DelegateID QuestionID
      10 1
      10 7
      >
      etc.
      >
      How do i write a query that selects delegates that have answered question
      1
      AND question 7?
      >
      I tried "select delegateID where questionID=1 AND questionID=7, but of
      course no records were returned since no records satisfied both question
      IDs.
      >
      Any help would be greatly appreciated.

      Comment

      • strawberry

        #4
        Re: constructing a sql query


        Dave wrote:
        I have tried that, but that will give a delegate id if they have completed
        either question, but i only want to get the delegate id if they have
        completed BOTH questions.
        >
        Thanks
        >
        "que" <varyform@gmail .comwrote in message
        news:1165926468 .004067.38290@1 6g2000cwy.googl egroups.com...
        questionID=1 OR questionID=7
        >
        Dave íàïèñàâ:
        Hi

        I'm having trouble with some sql and don't know if my table design is
        incorrect.

        I have a table of delegate names.

        I have a table of possible questions the delegate can answer.

        I then have a table which contains many rows for each delegate, each row
        showing which question the delegate has answered.

        So for example, delegate id 10 has answered question 1 and question 7, so
        there would be two rows

        DelegateID QuestionID
        10 1
        10 7

        etc.

        How do i write a query that selects delegates that have answered question
        1
        AND question 7?

        I tried "select delegateID where questionID=1 AND questionID=7, butof
        course no records were returned since no records satisfied both question
        IDs.

        Any help would be greatly appreciated.
        SELECT t1.`DelegateID`
        FROM mytable t1
        LEFT JOIN mytable t2 ON t1.`DelegateID` = t2.`DelegateID`
        WHERE t1.`QuestionID` = 1
        AND t2.`QuestionID` = 7

        Comment

        • Dave

          #5
          Re: constructing a sql query

          Thanks for the reply. but i can't make this work since questionID does not
          exist in t1


          "strawberry " <zac.carey@gmai l.comwrote in message
          news:1165930145 .656407.311150@ l12g2000cwl.goo glegroups.com.. .

          Dave wrote:
          I have tried that, but that will give a delegate id if they have completed
          either question, but i only want to get the delegate id if they have
          completed BOTH questions.
          >
          Thanks
          >
          "que" <varyform@gmail .comwrote in message
          news:1165926468 .004067.38290@1 6g2000cwy.googl egroups.com...
          questionID=1 OR questionID=7
          >
          Dave íàïèñàâ:
          Hi

          I'm having trouble with some sql and don't know if my table design is
          incorrect.

          I have a table of delegate names.

          I have a table of possible questions the delegate can answer.

          I then have a table which contains many rows for each delegate, each row
          showing which question the delegate has answered.

          So for example, delegate id 10 has answered question 1 and question 7,
          so
          there would be two rows

          DelegateID QuestionID
          10 1
          10 7

          etc.

          How do i write a query that selects delegates that have answered
          question
          1
          AND question 7?

          I tried "select delegateID where questionID=1 AND questionID=7, but of
          course no records were returned since no records satisfied both question
          IDs.

          Any help would be greatly appreciated.
          SELECT t1.`DelegateID`
          FROM mytable t1
          LEFT JOIN mytable t2 ON t1.`DelegateID` = t2.`DelegateID`
          WHERE t1.`QuestionID` = 1
          AND t2.`QuestionID` = 7


          Comment

          • strawberry

            #6
            Re: constructing a sql query


            Dave wrote:
            Thanks for the reply. but i can't make this work since questionID does not
            exist in t1
            >
            >
            "strawberry " <zac.carey@gmai l.comwrote in message
            news:1165930145 .656407.311150@ l12g2000cwl.goo glegroups.com.. .
            >
            Dave wrote:
            >
            I have tried that, but that will give a delegate id if they have completed
            either question, but i only want to get the delegate id if they have
            completed BOTH questions.

            Thanks

            "que" <varyform@gmail .comwrote in message
            news:1165926468 .004067.38290@1 6g2000cwy.googl egroups.com...
            questionID=1 OR questionID=7

            Dave íàïèñàâ:
            Hi
            >
            I'm having trouble with some sql and don't know if my table design is
            incorrect.
            >
            I have a table of delegate names.
            >
            I have a table of possible questions the delegate can answer.
            >
            I then have a table which contains many rows for each delegate, each row
            showing which question the delegate has answered.
            >
            So for example, delegate id 10 has answered question 1 and question 7,
            so
            there would be two rows
            >
            DelegateID QuestionID
            10 1
            10 7
            >
            etc.
            >
            How do i write a query that selects delegates that have answered
            question
            1
            AND question 7?
            >
            I tried "select delegateID where questionID=1 AND questionID=7, but of
            course no records were returned since no records satisfied both question
            IDs.
            >
            Any help would be greatly appreciated.
            >
            SELECT t1.`DelegateID`
            FROM mytable t1
            LEFT JOIN mytable t2 ON t1.`DelegateID` = t2.`DelegateID`
            WHERE t1.`QuestionID` = 1
            AND t2.`QuestionID` = 7

            ???

            What's the table with the question ids called?

            Comment

            • strawberry

              #7
              Re: constructing a sql query


              strawberry wrote:
              Dave wrote:
              >
              Thanks for the reply. but i can't make this work since questionID does not
              exist in t1


              "strawberry " <zac.carey@gmai l.comwrote in message
              news:1165930145 .656407.311150@ l12g2000cwl.goo glegroups.com.. .

              Dave wrote:
              I have tried that, but that will give a delegate id if they have completed
              either question, but i only want to get the delegate id if they have
              completed BOTH questions.
              >
              Thanks
              >
              "que" <varyform@gmail .comwrote in message
              news:1165926468 .004067.38290@1 6g2000cwy.googl egroups.com...
              questionID=1 OR questionID=7
              >
              Dave íàïèñàâ:
              Hi

              I'm having trouble with some sql and don't know if my table design is
              incorrect.

              I have a table of delegate names.

              I have a table of possible questions the delegate can answer.

              I then have a table which contains many rows for each delegate, each row
              showing which question the delegate has answered.

              So for example, delegate id 10 has answered question 1 and question7,
              so
              there would be two rows

              DelegateID QuestionID
              10 1
              10 7

              etc.

              How do i write a query that selects delegates that have answered
              question
              1
              AND question 7?

              I tried "select delegateID where questionID=1 AND questionID=7,bu t of
              course no records were returned since no records satisfied both question
              IDs.

              Any help would be greatly appreciated.
              SELECT t1.`DelegateID`
              FROM mytable t1
              LEFT JOIN mytable t2 ON t1.`DelegateID` = t2.`DelegateID`
              WHERE t1.`QuestionID` = 1
              AND t2.`QuestionID` = 7
              >
              >
              ???
              >
              What's the table with the question ids called?
              I mean this table:

              DelegateID QuestionID
              10 1
              10 7

              Comment

              • shakahshakah@gmail.com

                #8
                Re: constructing a sql query

                On Dec 12, 8:49 am, "Dave" <d...@nospam.co .ukwrote:
                Thanks for the reply. but i can't make this work since questionID does not
                exist in t1
                >
                "strawberry " <zac.ca...@gmai l.comwrote in messagenews:116 5930145.656407. .311150@l12g200 0cwl.googlegrou ps.com...
                >
                >
                >
                Dave wrote:
                I have tried that, but that will give a delegate id if they have completed
                either question, but i only want to get the delegate id if they have
                completed BOTH questions.
                >
                Thanks
                >
                "que" <varyf...@gmail .comwrote in message
                news:1165926468 .004067.38290@1 6g2000cwy.googl egroups.com...
                questionID=1 OR questionID=7
                >
                Dave íàïèñàâ:
                Hi
                >
                I'm having trouble with some sql and don't know if my table design is
                incorrect.
                >
                I have a table of delegate names.
                >
                I have a table of possible questions the delegate can answer.
                >
                I then have a table which contains many rows for each delegate, each row
                showing which question the delegate has answered.
                >
                So for example, delegate id 10 has answered question 1 and question 7,
                so
                there would be two rows
                >
                DelegateID QuestionID
                10 1
                10 7
                >
                etc.
                >
                How do i write a query that selects delegates that have answered
                question
                1
                AND question 7?
                >
                I tried "select delegateID where questionID=1 AND questionID=7, but of
                course no records were returned since no records satisfied both question
                IDs.
                >
                Any help would be greatly appreciated.SEL ECT t1.`DelegateID`
                FROM mytable t1
                LEFT JOIN mytable t2 ON t1.`DelegateID` = t2.`DelegateID`
                WHERE t1.`QuestionID` = 1
                AND t2.`QuestionID` = 7
                Either of the following work?

                -- ...if you're using a MySQL version without subselects
                SELECT d1.DelegateID
                FROM delegates d1
                LEFT JOIN questions q1 ON q1.DelegateID=d 1.DelegateID AND
                q1.QuestionID=1
                LEFT JOIN questions q2 ON q2.DelegateID=d 1.DelegateID AND
                q2.QuestionID=7 ;

                -- ...if you're using a MySQL version with subselects
                SELECT d.DelegateID
                FROM delegates d
                WHERE EXISTS (
                SELECT 1 FROM questions q
                WHERE q.DelegateID=d. DelegateID
                AND q.QuestionID=1
                )
                AND EXISTS (
                SELECT 1 FROM questions q
                WHERE q.DelegateID=d. DelegateID
                AND q.QuestionID=7
                ) ;

                Comment

                • shakahshakah@gmail.com

                  #9
                  Re: constructing a sql query

                  On Dec 12, 9:16 am, "shakahsha...@g mail.com" <shakahsha...@g mail.com>
                  wrote:
                  On Dec 12, 8:49 am, "Dave" <d...@nospam.co .ukwrote:
                  >
                  >
                  >
                  Thanks for the reply. but i can't make this work since questionID does not
                  exist in t1
                  >
                  "strawberry " <zac.ca...@gmai l.comwrote in messagenews:116 5930145.656407. 311150@l12g2000 cwl.googlegroup s.com...
                  >
                  Dave wrote:
                  I have tried that, but that will give a delegate id if they have completed
                  either question, but i only want to get the delegate id if they have
                  completed BOTH questions.
                  >
                  Thanks
                  >
                  "que" <varyf...@gmail .comwrote in message
                  >news:116592646 8.004067.38290@ 16g2000cwy.goog legroups.com...
                  questionID=1 OR questionID=7
                  >
                  Dave íàïèñàâ:
                  Hi
                  >
                  I'm having trouble with some sql and don't know if my table design is
                  incorrect.
                  >
                  I have a table of delegate names.
                  >
                  I have a table of possible questions the delegate can answer.
                  >
                  I then have a table which contains many rows for each delegate, each row
                  showing which question the delegate has answered.
                  >
                  So for example, delegate id 10 has answered question 1 and question7,
                  so
                  there would be two rows
                  >
                  DelegateID QuestionID
                  10 1
                  10 7
                  >
                  etc.
                  >
                  How do i write a query that selects delegates that have answered
                  question
                  1
                  AND question 7?
                  >
                  I tried "select delegateID where questionID=1 AND questionID=7,bu t of
                  course no records were returned since no records satisfied both question
                  IDs.
                  >
                  Any help would be greatly appreciated.SEL ECT t1.`DelegateID`
                  FROM mytable t1
                  LEFT JOIN mytable t2 ON t1.`DelegateID` = t2.`DelegateID`
                  WHERE t1.`QuestionID` = 1
                  AND t2.`QuestionID` = 7Either of the following work?
                  >
                  -- ...if you're using a MySQL version without subselects
                  SELECT d1.DelegateID
                  FROM delegates d1
                  LEFT JOIN questions q1 ON q1.DelegateID=d 1.DelegateID AND
                  q1.QuestionID=1
                  LEFT JOIN questions q2 ON q2.DelegateID=d 1.DelegateID AND
                  q2.QuestionID=7 ;
                  >
                  -- ...if you're using a MySQL version with subselects
                  SELECT d.DelegateID
                  FROM delegates d
                  WHERE EXISTS (
                  SELECT 1 FROM questions q
                  WHERE q.DelegateID=d. DelegateID
                  AND q.QuestionID=1
                  )
                  AND EXISTS (
                  SELECT 1 FROM questions q
                  WHERE q.DelegateID=d. DelegateID
                  AND q.QuestionID=7
                  ) ;
                  -- ...or, for that matter
                  SELECT q1.DelegateID
                  FROM questions q1
                  INNER JOIN questions q2
                  ON q2.DelegateID=q 1.DelegateID
                  AND q2.QuestionID=7
                  WHERE q1.QuestionID=1 ;

                  SELECT q1.DelegateID
                  FROM questions q1
                  WHERE q1.QuestionID=1
                  AND EXISTS (
                  SELECT 1 FROM questions q2
                  WHERE q2.DelegateID=q 1.DelegateID
                  AND q2.QuestionID=7
                  ) ;

                  Comment

                  • que

                    #10
                    Re: constructing a sql query

                    other, little bit tricky solution :)

                    select DelegateID, count(DelegateI D) c from your_table where QuestionID
                    in (1, 7)
                    group by DelegateID
                    having c=2
                    c=2 -- 2 - params count inside in()

                    eg: where QuestionID in (1, 2, 3, 4, 5) -- c=5

                    PS: one requirement - pair delegate-question must be uniq

                    Comment

                    • Dave

                      #11
                      Re: constructing a sql query

                      Thanks for staying with this one,

                      i have the delegate name table as t1, the question definitions table as t2,
                      and the joining table is now called mytable and that lists delegates and the
                      questions they have answered, but still no joy.

                      I don't get any data returned.

                      I also don't understand why you have

                      WHERE t1.`QuestionID` = 1 AND t2.`QuestionID` = 7

                      why does this reference t1 and t2?



                      "strawberry " <zac.carey@gmai l.comwrote in message
                      news:1165932982 .451804.313370@ j72g2000cwa.goo glegroups.com.. .

                      strawberry wrote:
                      Dave wrote:
                      >
                      Thanks for the reply. but i can't make this work since questionID does
                      not
                      exist in t1


                      "strawberry " <zac.carey@gmai l.comwrote in message
                      news:1165930145 .656407.311150@ l12g2000cwl.goo glegroups.com.. .

                      Dave wrote:
                      I have tried that, but that will give a delegate id if they have
                      completed
                      either question, but i only want to get the delegate id if they have
                      completed BOTH questions.
                      >
                      Thanks
                      >
                      "que" <varyform@gmail .comwrote in message
                      news:1165926468 .004067.38290@1 6g2000cwy.googl egroups.com...
                      questionID=1 OR questionID=7
                      >
                      Dave íàïèñàâ:
                      Hi

                      I'm having trouble with some sql and don't know if my table design
                      is
                      incorrect.

                      I have a table of delegate names.

                      I have a table of possible questions the delegate can answer.

                      I then have a table which contains many rows for each delegate, each
                      row
                      showing which question the delegate has answered.

                      So for example, delegate id 10 has answered question 1 and question
                      7,
                      so
                      there would be two rows

                      DelegateID QuestionID
                      10 1
                      10 7

                      etc.

                      How do i write a query that selects delegates that have answered
                      question
                      1
                      AND question 7?

                      I tried "select delegateID where questionID=1 AND questionID=7, but
                      of
                      course no records were returned since no records satisfied both
                      question
                      IDs.

                      Any help would be greatly appreciated.
                      SELECT t1.`DelegateID`
                      FROM mytable t1
                      LEFT JOIN mytable t2 ON t1.`DelegateID` = t2.`DelegateID`
                      WHERE t1.`QuestionID` = 1
                      AND t2.`QuestionID` = 7
                      >
                      >
                      ???
                      >
                      What's the table with the question ids called?
                      I mean this table:

                      DelegateID QuestionID
                      10 1
                      10 7


                      Comment

                      • strawberry

                        #12
                        Re: constructing a sql query


                        Dave wrote:
                        Thanks for staying with this one,
                        >
                        i have the delegate name table as t1, the question definitions table as t2,
                        and the joining table is now called mytable and that lists delegates and the
                        questions they have answered, but still no joy.
                        >
                        I don't get any data returned.
                        >
                        I also don't understand why you have
                        >
                        WHERE t1.`QuestionID` = 1 AND t2.`QuestionID` = 7
                        >
                        why does this reference t1 and t2?
                        >
                        >
                        >
                        "strawberry " <zac.carey@gmai l.comwrote in message
                        news:1165932982 .451804.313370@ j72g2000cwa.goo glegroups.com.. .
                        >
                        strawberry wrote:
                        >
                        Dave wrote:
                        Thanks for the reply. but i can't make this work since questionID does
                        not
                        exist in t1
                        >
                        >
                        "strawberry " <zac.carey@gmai l.comwrote in message
                        news:1165930145 .656407.311150@ l12g2000cwl.goo glegroups.com.. .
                        >
                        Dave wrote:
                        >
                        I have tried that, but that will give a delegate id if they have
                        completed
                        either question, but i only want to get the delegate id if they have
                        completed BOTH questions.

                        Thanks

                        "que" <varyform@gmail .comwrote in message
                        news:1165926468 .004067.38290@1 6g2000cwy.googl egroups.com...
                        questionID=1 OR questionID=7

                        Dave íàïèñàâ:
                        Hi
                        >
                        I'm having trouble with some sql and don't know if my table design
                        is
                        incorrect.
                        >
                        I have a table of delegate names.
                        >
                        I have a table of possible questions the delegate can answer.
                        >
                        I then have a table which contains many rows for each delegate, each
                        row
                        showing which question the delegate has answered.
                        >
                        So for example, delegate id 10 has answered question 1 and question
                        7,
                        so
                        there would be two rows
                        >
                        DelegateID QuestionID
                        10 1
                        10 7
                        >
                        etc.
                        >
                        How do i write a query that selects delegates that have answered
                        question
                        1
                        AND question 7?
                        >
                        I tried "select delegateID where questionID=1 AND questionID=7, but
                        of
                        course no records were returned since no records satisfied both
                        question
                        IDs.
                        >
                        Any help would be greatly appreciated.
                        >
                        SELECT t1.`DelegateID`
                        FROM mytable t1
                        LEFT JOIN mytable t2 ON t1.`DelegateID` = t2.`DelegateID`
                        WHERE t1.`QuestionID` = 1
                        AND t2.`QuestionID` = 7

                        ???

                        What's the table with the question ids called?
                        >
                        I mean this table:
                        >
                        DelegateID QuestionID
                        10 1
                        10 7
                        Try to avoid 'top-posting' (putting your reply after the respondent).

                        t1 and t2 both refer to the same table - the 'questions_dele gates'
                        table in this case - or whatever it is you've call it. The delegates
                        table is redundant for the purposes of this query.

                        Comment

                        • strawberry

                          #13
                          Re: constructing a sql query


                          strawberry wrote:
                          Dave wrote:
                          >
                          Thanks for staying with this one,

                          i have the delegate name table as t1, the question definitions table ast2,
                          and the joining table is now called mytable and that lists delegates and the
                          questions they have answered, but still no joy.

                          I don't get any data returned.

                          I also don't understand why you have

                          WHERE t1.`QuestionID` = 1 AND t2.`QuestionID` = 7

                          why does this reference t1 and t2?



                          "strawberry " <zac.carey@gmai l.comwrote in message
                          news:1165932982 .451804.313370@ j72g2000cwa.goo glegroups.com.. .

                          strawberry wrote:
                          Dave wrote:
                          >
                          Thanks for the reply. but i can't make this work since questionID does
                          not
                          exist in t1


                          "strawberry " <zac.carey@gmai l.comwrote in message
                          news:1165930145 .656407.311150@ l12g2000cwl.goo glegroups.com.. .

                          Dave wrote:

                          I have tried that, but that will give a delegate id if they have
                          completed
                          either question, but i only want to get the delegate id if they have
                          completed BOTH questions.
                          >
                          Thanks
                          >
                          "que" <varyform@gmail .comwrote in message
                          news:1165926468 .004067.38290@1 6g2000cwy.googl egroups.com...
                          questionID=1 OR questionID=7
                          >
                          Dave íàïèñàâ:
                          Hi

                          I'm having trouble with some sql and don't know if my table design
                          is
                          incorrect.

                          I have a table of delegate names.

                          I have a table of possible questions the delegate can answer.

                          I then have a table which contains many rows for each delegate,each
                          row
                          showing which question the delegate has answered.

                          So for example, delegate id 10 has answered question 1 and question
                          7,
                          so
                          there would be two rows

                          DelegateID QuestionID
                          10 1
                          10 7

                          etc.

                          How do i write a query that selects delegates that have answered
                          question
                          1
                          AND question 7?

                          I tried "select delegateID where questionID=1 AND questionID=7, but
                          of
                          course no records were returned since no records satisfied both
                          question
                          IDs.

                          Any help would be greatly appreciated.

                          SELECT t1.`DelegateID`
                          FROM mytable t1
                          LEFT JOIN mytable t2 ON t1.`DelegateID` = t2.`DelegateID`
                          WHERE t1.`QuestionID` = 1
                          AND t2.`QuestionID` = 7
                          >
                          >
                          ???
                          >
                          What's the table with the question ids called?
                          I mean this table:

                          DelegateID QuestionID
                          10 1
                          10 7
                          >
                          Try to avoid 'top-posting' (putting your reply after the respondent).
                          >
                          t1 and t2 both refer to the same table - the 'questions_dele gates'
                          table in this case - or whatever it is you've call it. The delegates
                          table is redundant for the purposes of this query.
                          Try to avoid 'top-posting' (putting your reply after the respondent).
                          Doh. Well, you know what I meant!

                          Comment

                          • Dave

                            #14
                            Re: constructing a sql query


                            "strawberry " <zac.carey@gmai l.comwrote in message
                            news:1165940370 .020772.125370@ f1g2000cwa.goog legroups.com...

                            strawberry wrote:
                            Dave wrote:
                            >
                            Thanks for staying with this one,

                            i have the delegate name table as t1, the question definitions table as
                            t2,
                            and the joining table is now called mytable and that lists delegates and
                            the
                            questions they have answered, but still no joy.

                            I don't get any data returned.

                            I also don't understand why you have

                            WHERE t1.`QuestionID` = 1 AND t2.`QuestionID` = 7

                            why does this reference t1 and t2?



                            "strawberry " <zac.carey@gmai l.comwrote in message
                            news:1165932982 .451804.313370@ j72g2000cwa.goo glegroups.com.. .

                            strawberry wrote:
                            Dave wrote:
                            >
                            Thanks for the reply. but i can't make this work since questionID
                            does
                            not
                            exist in t1


                            "strawberry " <zac.carey@gmai l.comwrote in message
                            news:1165930145 .656407.311150@ l12g2000cwl.goo glegroups.com.. .

                            Dave wrote:

                            I have tried that, but that will give a delegate id if they have
                            completed
                            either question, but i only want to get the delegate id if they
                            have
                            completed BOTH questions.
                            >
                            Thanks
                            >
                            "que" <varyform@gmail .comwrote in message
                            news:1165926468 .004067.38290@1 6g2000cwy.googl egroups.com...
                            questionID=1 OR questionID=7
                            >
                            Dave íàïèñàâ:
                            Hi

                            I'm having trouble with some sql and don't know if my table
                            design
                            is
                            incorrect.

                            I have a table of delegate names.

                            I have a table of possible questions the delegate can answer.

                            I then have a table which contains many rows for each delegate,
                            each
                            row
                            showing which question the delegate has answered.

                            So for example, delegate id 10 has answered question 1 and
                            question
                            7,
                            so
                            there would be two rows

                            DelegateID QuestionID
                            10 1
                            10 7

                            etc.

                            How do i write a query that selects delegates that have answered
                            question
                            1
                            AND question 7?

                            I tried "select delegateID where questionID=1 AND questionID=7,
                            but
                            of
                            course no records were returned since no records satisfied both
                            question
                            IDs.

                            Any help would be greatly appreciated.

                            SELECT t1.`DelegateID`
                            FROM mytable t1
                            LEFT JOIN mytable t2 ON t1.`DelegateID` = t2.`DelegateID`
                            WHERE t1.`QuestionID` = 1
                            AND t2.`QuestionID` = 7
                            >
                            >
                            ???
                            >
                            What's the table with the question ids called?
                            I mean this table:

                            DelegateID QuestionID
                            10 1
                            10 7
                            >
                            Try to avoid 'top-posting' (putting your reply after the respondent).
                            >
                            t1 and t2 both refer to the same table - the 'questions_dele gates'
                            table in this case - or whatever it is you've call it. The delegates
                            table is redundant for the purposes of this query.
                            Try to avoid 'top-posting' (putting your reply after the respondent).
                            Doh. Well, you know what I meant!
                            Ok, i seem to have that working now (although not exactly sure how that
                            works), but how is that now expandable to make it work with multiple
                            question, i.e to get delegates that have answered questions 1,2,3,7,8,10?

                            Thanks for your help



                            Comment

                            • strawberry

                              #15
                              Re: constructing a sql query


                              Dave wrote:
                              "strawberry " <zac.carey@gmai l.comwrote in message
                              news:1165940370 .020772.125370@ f1g2000cwa.goog legroups.com...
                              >
                              strawberry wrote:
                              >
                              Dave wrote:
                              Thanks for staying with this one,
                              >
                              i have the delegate name table as t1, the question definitions table as
                              t2,
                              and the joining table is now called mytable and that lists delegates and
                              the
                              questions they have answered, but still no joy.
                              >
                              I don't get any data returned.
                              >
                              I also don't understand why you have
                              >
                              WHERE t1.`QuestionID` = 1 AND t2.`QuestionID` = 7
                              >
                              why does this reference t1 and t2?
                              >
                              >
                              >
                              "strawberry " <zac.carey@gmai l.comwrote in message
                              news:1165932982 .451804.313370@ j72g2000cwa.goo glegroups.com.. .
                              >
                              strawberry wrote:
                              >
                              Dave wrote:

                              Thanks for the reply. but i can't make this work since questionID
                              does
                              not
                              exist in t1
                              >
                              >
                              "strawberry " <zac.carey@gmai l.comwrote in message
                              news:1165930145 .656407.311150@ l12g2000cwl.goo glegroups.com.. .
                              >
                              Dave wrote:
                              >
                              I have tried that, but that will give a delegate id if they have
                              completed
                              either question, but i only want to get the delegate id if they
                              have
                              completed BOTH questions.

                              Thanks

                              "que" <varyform@gmail .comwrote in message
                              news:1165926468 .004067.38290@1 6g2000cwy.googl egroups.com...
                              questionID=1 OR questionID=7

                              Dave íàïèñàâ:
                              Hi
                              >
                              I'm having trouble with some sql and don't know if my table
                              design
                              is
                              incorrect.
                              >
                              I have a table of delegate names.
                              >
                              I have a table of possible questions the delegate can answer.
                              >
                              I then have a table which contains many rows for each delegate,
                              each
                              row
                              showing which question the delegate has answered.
                              >
                              So for example, delegate id 10 has answered question 1 and
                              question
                              7,
                              so
                              there would be two rows
                              >
                              DelegateID QuestionID
                              10 1
                              10 7
                              >
                              etc.
                              >
                              How do i write a query that selects delegates that have answered
                              question
                              1
                              AND question 7?
                              >
                              I tried "select delegateID where questionID=1 AND questionID=7,
                              but
                              of
                              course no records were returned since no records satisfied both
                              question
                              IDs.
                              >
                              Any help would be greatly appreciated.
                              >
                              SELECT t1.`DelegateID`
                              FROM mytable t1
                              LEFT JOIN mytable t2 ON t1.`DelegateID` = t2.`DelegateID`
                              WHERE t1.`QuestionID` = 1
                              AND t2.`QuestionID` = 7


                              ???

                              What's the table with the question ids called?
                              >
                              I mean this table:
                              >
                              DelegateID QuestionID
                              10 1
                              10 7
                              Try to avoid 'top-posting' (putting your reply after the respondent).

                              t1 and t2 both refer to the same table - the 'questions_dele gates'
                              table in this case - or whatever it is you've call it. The delegates
                              table is redundant for the purposes of this query.
                              >
                              Try to avoid 'top-posting' (putting your reply after the respondent).
                              >
                              Doh. Well, you know what I meant!
                              >
                              Ok, i seem to have that working now (although not exactly sure how that
                              works), but how is that now expandable to make it work with multiple
                              question, i.e to get delegates that have answered questions 1,2,3,7,8,10?
                              >
                              Thanks for your help
                              Yes - but this might not be the most efficient way to do it. I may be
                              mistaken but my guess is that the query gets exponentially slower for
                              every additional condition. Anyhow the syntax would look like this:

                              I've added a few 'AS's - just to make it a bit clearer.

                              SELECT t1.`ID`
                              FROM mytable AS t1
                              LEFT JOIN mytable AS t2 ON t1.`TASK-ID` = t2.`TASK-ID`
                              LEFT JOIN mytable AS t3 ON t1.`TASK-ID` = t3.`TASK-ID`
                              ....
                              WHERE t1.`QuestionID` =4
                              AND t2.`QuestionID` =5
                              AND t3.`QuestionID` =6
                              ....

                              Comment

                              Working...