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

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

[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" <***@gmail.com> wrote in message news:***@16g2000cwy.googlegroups.com...
questionID=1 OR questionID=7

Post by 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]

Post by 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
questionID=1 OR questionID=7

Post by 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

[Dave]

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

Post by 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
questionID=1 OR questionID=7

Post by 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]

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

Post by 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
questionID=1 OR questionID=7

Post by 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]

Post by strawberry

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

Post by 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
questionID=1 OR questionID=7

Post by 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

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

Post by strawberry

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

Post by 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
questionID=1 OR questionID=7

Post by 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]

Post by 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?

Post by strawberry

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

Post by 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
questionID=1 OR questionID=7

Post by 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?

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]

Post by strawberry

Post by 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?

Post by strawberry

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

Post by 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
questionID=1 OR questionID=7

Post by 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?

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]

Post by strawberry

Post by 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?

Post by strawberry

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

Post by 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
questionID=1 OR questionID=7

Post by 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?

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]

Post by Dave

Post by strawberry

Post by 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?

Post by strawberry

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

Post by 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
questionID=1 OR questionID=7

Post by 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?

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]

Post by Dave

Post by strawberry

Post by 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?

Post by strawberry

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

Post by 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
questionID=1 OR questionID=7

Post by 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?

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

[s***@gmail.com]

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

Post by 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
questionID=1 OR questionID=7

Post by 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
) ;

[s***@gmail.com]

Post by s***@gmail.com

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

Post by 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
questionID=1 OR questionID=7

Post by 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` = 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]

This worked well, thank you

Post by 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