constructing a sql query

[Dave]

Re: constructing a sql query

This worked well, thank you

"que" <varyform@gmail .comwrote in message
news:1165935530 .020272.181310@ 80g2000cwy.goog legroups.com...

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
>

[Dave]

Re: constructing a sql query


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

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


Thanks for all of your help, the problem is now solved