Solved

replace intersect in SQL query

Posted on 2004-10-27
6,231 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
Question by:ramazanyich
    5 Comments
     
    LVL 19

    Author Comment

    by:ramazanyich
    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:
    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
    oops - missed the parens around the last SELECT statement - sorry !
    0
     
    LVL 19

    Author Comment

    by:ramazanyich
    Thanks it works fine
    0
     

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Introduction A frequent design pattern question for new PHP developers goes something like this, "How do I handle client registration and login?"  It's done in every framework and CMS, and all of them use a similar pattern.  This article builds the…
    I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
    This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    860 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now