Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

replace intersect in SQL query

Posted on 2004-10-27
5
Medium Priority
?
6,279 Views
Last Modified: 2008-11-18
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 ?


0
Comment
Question by:ramazanyich
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 19

Author Comment

by:ramazanyich
ID: 12423200
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
 
LVL 16

Accepted Solution

by:
DcpKing earned 2000 total points
ID: 12423988
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
 
LVL 16

Expert Comment

by:DcpKing
ID: 12424006
oops - missed the parens around the last SELECT statement - sorry !
0
 
LVL 19

Author Comment

by:ramazanyich
ID: 12430954
Thanks it works fine
0
 

Expert Comment

by:Kenny_Y
ID: 22987490
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question