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?
 
DcpKingConnect With a Mentor Commented:
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
 
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:
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
All Courses

From novice to tech pro — start learning today.