#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    118
    Rep Power
    50

    Question Find the syntax error: select where IN subquery-union


    I have a query that looks right to me but MySQL is giving me a syntax error.

    Code:
    SELECT ObjectID FROM ObjectGroups WHERE GroupID IN
    ((SELECT GroupID FROM Groups WHERE GroupOwner='sally')
    UNION
    (SELECT Groups.GroupID AS GroupID FROM GroupMembers 
     JOIN Groups ON GroupMembers.GroupID=Groups.GroupID 
     WHERE GroupMembers.Member='sally'))
    The UNION of the subquery works by itself. The query with IN works with either half of the query. But fails when the UNION is the subquery.

    DETAILS

    Four tables. Objects, Groups, GroupMembers, and ObjectGroups.

    Groups are groups of users. Users can be in more than one group. If you "Own" a group, you are automatically a member of that group (i.e., not on the groupmember table).

    Objects can be assigned to an arbitrary number of groups. Thus, the ObjectGroups table.

    Here are the tables:

    Code:
    Create Table Groups 
    (
      GroupID int not null auto_increment primary key,
      GroupName varchar(100) not null, 
      GroupOwner varchar(22) not NULL
    );
    
    Create Table GroupMembers 
    (
      GroupID int unsigned not NULL,
      Member varchar(22) not NULL,
      PRIMARY KEY (GroupID, Member)
    );
    
    Create Table ObjectGroups 
    (
      ObjectID int unsigned not NULL,
      GroupID int unsigned not NULL,
      PRIMARY KEY (ObjectID, GroupID)
    );
    
    Create Table Objects 
    (
      ObjectID int not null auto_increment primary key,
      ObjectName varchar(100) not null, 
      ObjectOwner varchar(22) not NULL
    );
    The issue is to find all the Objects that belong to my Groups.

    Getting objects in a group is simple enough:
    Code:
    SELECT ObjectID, ObjectName FROM Objects
    WHERE ObjectID IN 
    (SELECT ObjectID FROM ObjectGroups WHERE GroupID=1)
    Finding out my Groups requires a UNION:

    Code:
    (SELECT GroupID FROM Groups WHERE GroupOwner='sally')
    UNION
    (SELECT Groups.GroupID AS GroupID FROM GroupMembers
     JOIN Groups ON GroupMembers.GroupID=Groups.GroupID
     WHERE GroupMembers.Member='sally')
    That's what I am using as the subquery to IN for getting a list of GroupIDs that are in my Groups.

    Code:
    SELECT ObjectID FROM ObjectGroups WHERE GroupID IN
    ((SELECT GroupID FROM Groups WHERE GroupOwner='sally')
    UNION
    (SELECT Groups.GroupID AS GroupID FROM GroupMembers
     JOIN Groups ON GroupMembers.GroupID=Groups.GroupID 
     WHERE GroupMembers.Member='sally'))
    This is where I stumble into the syntax error.

    I suspect perhaps what I am doing is unsupported syntax. But it is saying syntax error not syntax unsupported.

    ??
    Last edited by EEsterling; March 5th, 2013 at 12:34 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    do you want us to guess what the syntax error message was, or is there a chance you could share it with us?

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    118
    Rep Power
    50
    It is generic:
    Code:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT Groups.GroupID AS GroupID FROM GroupMembers JOIN Groups ON GroupMe...'
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by EEsterling
    ...near 'UNION
    well, that gives us a bit of a pointer

    try it like this --
    Code:
    SELECT ObjectID
      FROM ObjectGroups 
    INNER
      JOIN ( SELECT GroupID 
               FROM Groups 
              WHERE GroupOwner = 'sally'
             UNION
             SELECT Groups.GroupID 
               FROM GroupMembers
             INNER 
               JOIN Groups 
                 ON Groups.GroupID = GroupMembers.GroupID
              WHERE GroupMembers.Member = 'sally'
           ) AS u  
        ON u.GroupID = ObjectGroups.GroupID
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    118
    Rep Power
    50
    Okay. That work-around works.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    118
    Rep Power
    50
    Huh. I just tried the original query, but dropping the inner parentheses and that worked too.
    Code:
    SELECT ObjectID FROM ObjectGroups WHERE GroupID IN
    (SELECT GroupID FROM Groups WHERE GroupOwner='sally'
    UNION
    SELECT Groups.GroupID AS GroupID FROM GroupMembers
     JOIN Groups ON GroupMembers.GroupID=Groups.GroupID
     WHERE GroupMembers.Member='sally')
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    sweet
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo