replace intersect in SQL query

I have an SQL query which works in Oracle:
SELECT profiles.ID FROM profiles profiles, profileprops pro, security_property sec
WHERE
(
profiles.ID = pro.profileid
      AND sec.ID = pro.propertyid AND sec.NAME = 'userpassword'
      AND pro.VALUE = 'password'

)
intersect
SELECT profiles.ID FROM profiles profiles, profileprops pro, security_property sec
WHERE
(
profiles.ID = pro.profileid
      AND sec.ID = pro.propertyid AND sec.NAME = 'jcusername'
      AND pro.VALUE = 'user1'

)


As intersect is not supported in MySQL, could you suggest replacement of that query ?


LVL 19
ramazanyichAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ramazanyichAuthor Commented:
Addition: It can be not only one intersect. It is constructed dynamically and can contain multiple intersects, eg:
SELECT profiles.ID FROM profiles profiles, profileprops pro, security_property sec
WHERE
(
profiles.ID = pro.profileid
     AND sec.ID = pro.propertyid AND sec.NAME = 'userpassword'
     AND pro.VALUE = 'password'

)
intersect
SELECT profiles.ID FROM profiles profiles, profileprops pro, security_property sec
WHERE
(
profiles.ID = pro.profileid
     AND sec.ID = pro.propertyid AND sec.NAME = 'jcusername'
     AND pro.VALUE = 'user1'

)
...

intersect
SELECT profiles.ID FROM profiles profiles, profileprops pro, security_property sec
WHERE
(
profiles.ID = pro.profileid
     AND sec.ID = pro.propertyid AND sec.NAME = 'jcprop'
     AND pro.VALUE = 'value'

)

0
DcpKingCommented:
Do you get any reseults at all from this query in Oracle ? It seems to me that the three statements in your last INTERSECT example appear to be all mutually exclusive. They all have this in common:

SELECT profiles.ID FROM profiles profiles, profileprops pro, security_property sec
WHERE
( profiles.ID = pro.profileid AND sec.ID = pro.propertyid)

but then you ask for the intersection of the resultsets where

sec.NAME                         pro.VALUE

'userpassword'                 'password'
'jcusername'                    'user1'
'jcprop'                            'value'

I would have expected you to get results here using UNION but not INTERSECT

However, I think this will do what you are asking:

SELECT profiles.ID
FROM profiles profiles, profileprops pro, security_property sec
WHERE (      profiles.ID = pro.profileid and
                  sec.ID = pro.Propertyid and
         sec.NAME = 'userpassword' and
                  pro.VALUE = 'password' )
AND

profiles.ID in

(SELECT distinct profiles.ID
FROM profiles profiles, profileprops pro, security_property sec
WHERE  ( profiles.ID = pro.profileid
                 AND sec.ID = pro.propertyid
                    AND sec.NAME = 'jcusername'
                 AND pro.VALUE = 'user1')
)

AND

profiles.ID in

SELECT distinct profiles.ID
FROM profiles profiles, profileprops pro, security_property sec
WHERE (      profiles.ID = pro.profileid
                 AND sec.ID = pro.propertyid
                    AND sec.NAME = 'jcusername'
                 AND pro.VALUE = 'user1')


bear in mind that you will need MySQL 4.1 or later, that includes subQueries.

hth

Mike


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DcpKingCommented:
oops - missed the parens around the last SELECT statement - sorry !
0
ramazanyichAuthor Commented:
Thanks it works fine
0
Kenny_YCommented:
This completes my search for set operations with sql. Union is in the standard. Intersection is achieved with in-subquery. Complement with not-in subquery (minus in Oracle).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.