SEO

I use the following select statement that correctly retrieves the data
I want:

SELECT * FROM userpref AS pref
JOIN userpref AS pref2 ON pref2.userid=pref.userid
WHERE ( pref.questionid = '30' and pref.answer = '7507')
and( pref2.questionid = '41' and pref2.answer = '9108' )

The problem is that some of my select statements get rather complicated
and I use a lot of self-joining to the userpref table above, perhaps a
dozen or so self joins. This causes the select statements to really
sloooow down, taking a few minutes in some cases. Since this is used by
a website, this is not a good solution.
I thought I could improve performance if I were to trash the joins
and somehow do all the logic in the WHERE statement as:

SELECT * FROM userpref AS pref
WHERE ( pref.questionid = '30' and pref.answer = '7507')
and ( pref.questionid = '41' and pref.answer = '9108' )

Of course this doens't work, as the same questionid is referenced
twice, but my question is, is there some syntax here that would give
the same result as the join above but without using the join and have
the logic in the where statement alone?
Thanks

ukr_bend@yahoo.com wrote:
> SELECT * FROM userpref AS pref
> WHERE ( pref.questionid = '30' and pref.answer = '7507')
> and ( pref.questionid = '41' and pref.answer = '9108' )
>
> Of course this doens't work, as the same questionid is referenced
> twice, but my question is, is there some syntax here that would give
> the same result as the join above but without using the join and have
> the logic in the where statement alone?

The easiest solution is to use OR instead of AND:

SELECT * FROM userpref AS pref
WHERE ( pref.questionid = '30' and pref.answer = '7507' )
OR ( pref.questionid = '41' and pref.answer = '9108' )

Of course this produces a result set of more than one row, but then you
can loop through the result set in your application code and format it
the way you want.

Regards,
Bill K.

Thanks Bill. Could be the solution. But what I need are rows that only
meet all 4 conditions of the where statement. But like you said,
perhaps retrieve everything then do the grunt work in the app.

Not seeing any details about the table layout or what the end goal is,
I can only speculate and give you a method that I would choose.

It appears that this is a table that stores user preferences and then
does something on the display based on those preferences.

create table pref (uid integer, prefq integer, prefanswer integer) !!
I prefer to use integers where value will always be a number.
create table userinfo (uid integer, username varchar, fname varchar,
lastname varchar....)
questiontbl
answtbl

insert into pref values (1,30,7507)
insert into pref values (1,41,9108)
insert into pref values (1,45,10032)
insert into pref values (2,30,7506)
insert into pref values (2,41,9107)
insert into pref values (2,45,10031)

now I can select/join them all together in one query.

select a.uid, b.prefq, c.prefanswer
from pref a, questiontbl b,anstbl c
where a.uid=123 and b.prefq=a.prefq and c.prefanswer=a.prefanswer

Now, all you need is the uid (user id) and you can retrieve all of the
questions and all of the answers in one query. And this would be very
fast as these "primary keys" would/could be indexed making retrieval
very fast.

One thing you will need in any case like this would be a way to ensure
that answer xyz goes with question 123 etc...

As always, YMMV

ukr_bend@yahoo.com wrote:
> Thanks Bill. Could be the solution. But what I need are rows that only
> meet all 4 conditions of the where statement. But like you said,
> perhaps retrieve everything then do the grunt work in the app.

There's a HAVING COUNT technique that I have seen used, but I have
always thought it is kind of delicate and too easy to get wrong.

SELECT pref.userid FROM userpref AS pref
WHERE ( pref.questionid = '30' and pref.answer = '7507' )
OR ( pref.questionid = '41' and pref.answer = '9108' )
GROUP BY userid
HAVING COUNT(*) = 2

Change the value 2 to another value, matching the number of questionid
terms you have in your WHERE clause.

Regards,
Bill K.