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.

Re: constructing a sql query

questionID=3D1 OR questionID=3D7

Dave íàïèñà=E2:
> 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=3D1 AND questionID=3D7, but of
> course no records were returned since no records satisfied both question
> IDs.
>=20
> Any help would be greatly appreciated.

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" wrote in message
news:1165926468.004067.38290@16g2000cwy.googlegroups.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.

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" wrote in message
> news:1165926468.004067.38290@16g2000cwy.googlegroups.com...
> questionID=3D1 OR questionID=3D7
>
> Dave íàïèñà=E2:
> > 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 questi=
on
> > 1
> > AND question 7?
> >
> > I tried "select delegateID where questionID=3D1 AND questionID=3D7, 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` =3D t2.`DelegateID`
WHERE t1.`QuestionID` =3D 1
AND t2.`QuestionID` =3D 7

Re: constructing a sql query

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


"strawberry" wrote in message
news:1165930145.656407.311150@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" wrote in message
> news:1165926468.004067.38290@16g2000cwy.googlegroups.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

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" wrote in message
> news:1165930145.656407.311150@l12g2000cwl.googlegroups.com.. .
>
> Dave wrote:
>
> > I have tried that, but that will give a delegate id if they have comple=
ted
> > either question, but i only want to get the delegate id if they have
> > completed BOTH questions.
> >
> > Thanks
> >
> > "que" wrote in message
> > news:1165926468.004067.38290@16g2000cwy.googlegroups.com...
> > questionID=3D1 OR questionID=3D7
> >
> > Dave íàïèñà=E2:
> > > 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=3D1 AND questionID=3D7, b=
ut of
> > > course no records were returned since no records satisfied both quest=
ion
> > > IDs.
> > >
> > > Any help would be greatly appreciated.
>
> SELECT t1.`DelegateID`
> FROM mytable t1
> LEFT JOIN mytable t2 ON t1.`DelegateID` =3D t2.`DelegateID`
> WHERE t1.`QuestionID` =3D 1
> AND t2.`QuestionID` =3D 7


???

What's the table with the question ids called?

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" wrote in message
> > news:1165930145.656407.311150@l12g2000cwl.googlegroups.com.. .
> >
> > Dave wrote:
> >
> > > I have tried that, but that will give a delegate id if they have comp=
leted
> > > either question, but i only want to get the delegate id if they have
> > > completed BOTH questions.
> > >
> > > Thanks
> > >
> > > "que" wrote in message
> > > news:1165926468.004067.38290@16g2000cwy.googlegroups.com...
> > > questionID=3D1 OR questionID=3D7
> > >
> > > Dave íàïèñà=E2:
> > > > 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, eac=
h 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=3D1 AND questionID=3D7,=
but of
> > > > course no records were returned since no records satisfied both que=
stion
> > > > IDs.
> > > >
> > > > Any help would be greatly appreciated.
> >
> > SELECT t1.`DelegateID`
> > FROM mytable t1
> > LEFT JOIN mytable t2 ON t1.`DelegateID` =3D t2.`DelegateID`
> > WHERE t1.`QuestionID` =3D 1
> > AND t2.`QuestionID` =3D 7
>
>
> ???
>
> What's the table with the question ids called?

I mean this table:

DelegateID QuestionID
10 1
10 7

Re: constructing a sql query

On Dec 12, 8:49 am, "Dave" wrote:
> Thanks for the reply. but i can't make this work since questionID does not
> exist in t1
>
> "strawberry" wrote in messagenews:1165930145.656407=
..311150@l12g2000cwl.googlegroups.com...
>
>
>
> Dave wrote:
> > I have tried that, but that will give a delegate id if they have comple=
ted
> > either question, but i only want to get the delegate id if they have
> > completed BOTH questions.
>
> > Thanks
>
> > "que" wrote in message
> >news:1165926468.004067.38290@16g2000cwy.googlegroups.com...
> > questionID=3D1 OR questionID=3D7
>
> > Dave íàïèñà=E2:
> > > 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=3D1 AND questionID=3D7, b=
ut of
> > > course no records were returned since no records satisfied both quest=
ion
> > > IDs.
>
> > > Any help would be greatly appreciated.SELECT t1.`DelegateID`
> FROM mytable t1
> LEFT JOIN mytable t2 ON t1.`DelegateID` =3D t2.`DelegateID`
> WHERE t1.`QuestionID` =3D 1
> AND t2.`QuestionID` =3D 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=3Dd1.DelegateID AND
q1.QuestionID=3D1
LEFT JOIN questions q2 ON q2.DelegateID=3Dd1.DelegateID AND
q2.QuestionID=3D7 ;

-- ...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=3Dd.DelegateID
AND q.QuestionID=3D1
)
AND EXISTS (
SELECT 1 FROM questions q
WHERE q.DelegateID=3Dd.DelegateID
AND q.QuestionID=3D7
) ;

Re: constructing a sql query

On Dec 12, 9:16 am, "shakahsha...@gmail.com"
wrote:
> On Dec 12, 8:49 am, "Dave" wrote:
>
>
>
> > Thanks for the reply. but i can't make this work since questionID does =
not
> > exist in t1
>
> > "strawberry" wrote in messagenews:1165930145.6564=
07.311150@l12g2000cwl.googlegroups.com...
>
> > Dave wrote:
> > > I have tried that, but that will give a delegate id if they have comp=
leted
> > > either question, but i only want to get the delegate id if they have
> > > completed BOTH questions.
>
> > > Thanks
>
> > > "que" wrote in message
> > >news:1165926468.004067.38290@16g2000cwy.googlegroups.com...
> > > questionID=3D1 OR questionID=3D7
>
> > > Dave íàïèñà=E2:
> > > > 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, eac=
h 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=3D1 AND questionID=3D7,=
but of
> > > > course no records were returned since no records satisfied both que=
stion
> > > > IDs.
>
> > > > Any help would be greatly appreciated.SELECT t1.`DelegateID`
> > FROM mytable t1
> > LEFT JOIN mytable t2 ON t1.`DelegateID` =3D t2.`DelegateID`
> > WHERE t1.`QuestionID` =3D 1
> > AND t2.`QuestionID` =3D 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=3Dd1.DelegateID AND
> q1.QuestionID=3D1
> LEFT JOIN questions q2 ON q2.DelegateID=3Dd1.DelegateID AND
> q2.QuestionID=3D7 ;
>
> -- ...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=3Dd.DelegateID
> AND q.QuestionID=3D1
> )
> AND EXISTS (
> SELECT 1 FROM questions q
> WHERE q.DelegateID=3Dd.DelegateID
> AND q.QuestionID=3D7
> ) ;

-- ...or, for that matter
SELECT q1.DelegateID
FROM questions q1
INNER JOIN questions q2
ON q2.DelegateID=3Dq1.DelegateID
AND q2.QuestionID=3D7
WHERE q1.QuestionID=3D1 ;

SELECT q1.DelegateID
FROM questions q1
WHERE q1.QuestionID=3D1
AND EXISTS (
SELECT 1 FROM questions q2
WHERE q2.DelegateID=3Dq1.DelegateID
AND q2.QuestionID=3D7
) ;

Re: constructing a sql query

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

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" wrote in message
news:1165932982.451804.313370@j72g2000cwa.googlegroups.com.. .

strawberry wrote:

> Dave wrote:
>
> > Thanks for the reply. but i can't make this work since questionID does
> > not
> > exist in t1
> >
> >
> > "strawberry" wrote in message
> > news:1165930145.656407.311150@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" wrote in message
> > > news:1165926468.004067.38290@16g2000cwy.googlegroups.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

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 t=
2,
> 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` =3D 1 AND t2.`QuestionID` =3D 7
>
> why does this reference t1 and t2?
>
>
>
> "strawberry" wrote in message
> news:1165932982.451804.313370@j72g2000cwa.googlegroups.com.. .
>
> strawberry wrote:
>
> > Dave wrote:
> >
> > > Thanks for the reply. but i can't make this work since questionID does
> > > not
> > > exist in t1
> > >
> > >
> > > "strawberry" wrote in message
> > > news:1165930145.656407.311150@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" wrote in message
> > > > news:1165926468.004067.38290@16g2000cwy.googlegroups.com...
> > > > questionID=3D1 OR questionID=3D7
> > > >
> > > > Dave íàïèñà=E2:
> > > > > 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, e=
ach
> > > > > row
> > > > > showing which question the delegate has answered.
> > > > >
> > > > > So for example, delegate id 10 has answered question 1 and questi=
on
> > > > > 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=3D1 AND questionID=3D=
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` =3D t2.`DelegateID`
> > > WHERE t1.`QuestionID` =3D 1
> > > AND t2.`QuestionID` =3D 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.

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 as=
t2,
> > and the joining table is now called mytable and that lists delegates an=
d 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` =3D 1 AND t2.`QuestionID` =3D 7
> >
> > why does this reference t1 and t2?
> >
> >
> >
> > "strawberry" wrote in message
> > news:1165932982.451804.313370@j72g2000cwa.googlegroups.com.. .
> >
> > strawberry wrote:
> >
> > > Dave wrote:
> > >
> > > > Thanks for the reply. but i can't make this work since questionID d=
oes
> > > > not
> > > > exist in t1
> > > >
> > > >
> > > > "strawberry" wrote in message
> > > > news:1165930145.656407.311150@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 h=
ave
> > > > > completed BOTH questions.
> > > > >
> > > > > Thanks
> > > > >
> > > > > "que" wrote in message
> > > > > news:1165926468.004067.38290@16g2000cwy.googlegroups.com...
> > > > > questionID=3D1 OR questionID=3D7
> > > > >
> > > > > Dave íàïèñà=E2:
> > > > > > Hi
> > > > > >
> > > > > > I'm having trouble with some sql and don't know if my table des=
ign
> > > > > > 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 ques=
tion
> > > > > > 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=3D1 AND questionID=
=3D7, 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` =3D t2.`DelegateID`
> > > > WHERE t1.`QuestionID` =3D 1
> > > > AND t2.`QuestionID` =3D 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!

Re: constructing a sql query

"strawberry" wrote in message
news:1165940370.020772.125370@f1g2000cwa.googlegroups.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" wrote in message
> > news:1165932982.451804.313370@j72g2000cwa.googlegroups.com.. .
> >
> > strawberry wrote:
> >
> > > Dave wrote:
> > >
> > > > Thanks for the reply. but i can't make this work since questionID
> > > > does
> > > > not
> > > > exist in t1
> > > >
> > > >
> > > > "strawberry" wrote in message
> > > > news:1165930145.656407.311150@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" wrote in message
> > > > > news:1165926468.004067.38290@16g2000cwy.googlegroups.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

Re: constructing a sql query

Dave wrote:

> "strawberry" wrote in message
> news:1165940370.020772.125370@f1g2000cwa.googlegroups.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` =3D 1 AND t2.`QuestionID` =3D 7
> > >
> > > why does this reference t1 and t2?
> > >
> > >
> > >
> > > "strawberry" wrote in message
> > > news:1165932982.451804.313370@j72g2000cwa.googlegroups.com.. .
> > >
> > > strawberry wrote:
> > >
> > > > Dave wrote:
> > > >
> > > > > Thanks for the reply. but i can't make this work since questionID
> > > > > does
> > > > > not
> > > > > exist in t1
> > > > >
> > > > >
> > > > > "strawberry" wrote in message
> > > > > news:1165930145.656407.311150@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" wrote in message
> > > > > > news:1165926468.004067.38290@16g2000cwy.googlegroups.com...
> > > > > > questionID=3D1 OR questionID=3D7
> > > > > >
> > > > > > Dave íàïèñà=E2:
> > > > > > > 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 delegat=
e,
> > > > > > > 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 answe=
red
> > > > > > > question
> > > > > > > 1
> > > > > > > AND question 7?
> > > > > > >
> > > > > > > I tried "select delegateID where questionID=3D1 AND questionI=
D=3D7,
> > > > > > > but
> > > > > > > of
> > > > > > > course no records were returned since no records satisfied bo=
th
> > > > > > > question
> > > > > > > IDs.
> > > > > > >
> > > > > > > Any help would be greatly appreciated.
> > > > >
> > > > > SELECT t1.`DelegateID`
> > > > > FROM mytable t1
> > > > > LEFT JOIN mytable t2 ON t1.`DelegateID` =3D t2.`DelegateID`
> > > > > WHERE t1.`QuestionID` =3D 1
> > > > > AND t2.`QuestionID` =3D 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` =3D t2.`TASK-ID`
LEFT JOIN mytable AS t3 ON t1.`TASK-ID` =3D t3.`TASK-ID`
...
WHERE t1.`QuestionID` =3D4
AND t2.`QuestionID` =3D5
AND t3.`QuestionID` =3D6
...

Re: constructing a sql query

This worked well, thank you

"que" wrote in message
news:1165935530.020272.181310@80g2000cwy.googlegroups.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
>

Re: constructing a sql query

"strawberry" wrote in message
news:1165943610.848022.110840@j72g2000cwa.googlegroups.com.. .

Dave wrote:

> "strawberry" wrote in message
> news:1165940370.020772.125370@f1g2000cwa.googlegroups.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" wrote in message
> > > news:1165932982.451804.313370@j72g2000cwa.googlegroups.com.. .
> > >
> > > strawberry wrote:
> > >
> > > > Dave wrote:
> > > >
> > > > > Thanks for the reply. but i can't make this work since questionID
> > > > > does
> > > > > not
> > > > > exist in t1
> > > > >
> > > > >
> > > > > "strawberry" wrote in message
> > > > > news:1165930145.656407.311150@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" wrote in message
> > > > > > news:1165926468.004067.38290@16g2000cwy.googlegroups.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