constructing a sql query

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

[que]

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.

[Dave]

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" <va******@gmail.comwrote in message
news:11*********************@16g2000cwy.googlegrou ps.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.

[strawberry]

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" <va******@gmail.comwrote in message
news:11*********************@16g2000cwy.googlegrou ps.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

[Dave]

Thanks for the reply. but i can't make this work since questionID does not
exist in t1
"strawberry" <za*******@gmail.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.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" <va******@gmail.comwrote in message
news:11*********************@16g2000cwy.googlegrou ps.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

[strawberry]

Dave wrote:

Thanks for the reply. but i can't make this work since questionID does not
exist in t1
"strawberry" <za*******@gmail.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.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" <va******@gmail.comwrote in message
news:11*********************@16g2000cwy.googlegrou ps.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?

[strawberry]

strawberry wrote:

Dave wrote:

Thanks for the reply. but i can't make this work since questionID does not
exist in t1
"strawberry" <za*******@gmail.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.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" <va******@gmail.comwrote in message
news:11*********************@16g2000cwy.googlegrou ps.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,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

[shakahshakah]

On Dec 12, 8:49 am, "Dave" <[e-mail]:

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

"strawberry" <[e-mail] in messagenews:1165930145.656407..31****@l12g2000cwl. googlegroups.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" <[e-mail] in message
news:11*********************@16g2000cwy.googlegrou ps.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

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=d1.DelegateID AND
q1.QuestionID=1
LEFT JOIN questions q2 ON q2.DelegateID=d1.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
) ;

[shakahshakah]

On Dec 12, 9:16 am, "[e-mail]" <[e-mail]>
wrote:

On Dec 12, 8:49 am, "Dave" <[e-mail]:

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

"strawberry" <[e-mail] in messagenews:11**********************@l12g2000cwl.g ooglegroups.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" <[e-mail] in message
>news:11*********************@16g2000cwy.googlegro ups.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,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` = 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=d1.DelegateID AND
q1.QuestionID=1
LEFT JOIN questions q2 ON q2.DelegateID=d1.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=q1.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=q1.DelegateID
AND q2.QuestionID=7
) ;

[que]

other, little bit tricky solution :)

select DelegateID, count(DelegateID) 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]

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" <za*******@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...

strawberry wrote:

Dave wrote:

Thanks for the reply. but i can't make this work since questionID does
not
exist in t1
"strawberry" <za*******@gmail.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.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" <va******@gmail.comwrote in message
news:11*********************@16g2000cwy.googlegrou ps.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

[strawberry]

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" <za*******@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...

strawberry wrote:

Dave wrote:

Thanks for the reply. but i can't make this work since questionID does
not
exist in t1
>
>
"strawberry" <za*******@gmail.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.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" <va******@gmail.comwrote in message
news:11*********************@16g2000cwy.googlegrou ps.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_delegates'
table in this case - or whatever it is you've call it. The delegates
table is redundant for the purposes of this query.

[strawberry]

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" <za*******@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...

strawberry wrote:

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


"strawberry" <za*******@gmail.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.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" <va******@gmail.comwrote in message
news:11*********************@16g2000cwy.googlegrou ps.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_delegates'
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!

[Dave]

"strawberry" <za*******@gmail.comwrote in message
news:11**********************@f1g2000cwa.googlegro ups.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" <za*******@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...

strawberry wrote:

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


"strawberry" <za*******@gmail.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.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" <va******@gmail.comwrote in message
news:11*********************@16g2000cwy.googlegrou ps.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_delegates'
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

[strawberry]

Dave wrote:

"strawberry" <za*******@gmail.comwrote in message
news:11**********************@f1g2000cwa.googlegro ups.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" <za*******@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...
>
strawberry wrote:
>
Dave wrote:

Thanks for the reply. but i can't make this work since questionID
does
not
exist in t1
>
>
"strawberry" <za*******@gmail.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.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" <va******@gmail.comwrote in message
news:11*********************@16g2000cwy.googlegrou ps.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_delegates'
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
....

[Dave]

This worked well, thank you

"que" <va******@gmail.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...

other, little bit tricky solution :)

select DelegateID, count(DelegateID) 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]

"strawberry" <za*******@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...

Dave wrote:

"strawberry" <za*******@gmail.comwrote in message
news:11**********************@f1g2000cwa.googlegro ups.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" <za*******@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...
>
strawberry wrote:
>
Dave wrote:

Thanks for the reply. but i can't make this work since questionID
does
not
exist in t1
>
>
"strawberry" <za*******@gmail.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.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" <va******@gmail.comwrote in message
news:11*********************@16g2000cwy.googlegrou ps.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_delegates'
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