Find the syntax error: select where IN subquery-union

Dev Shed Forums Sponsor:

March 5th, 2013, 12:30 PM

EEsterling

Contributing User

Join Date: Feb 2011

Posts: 97

Time spent in forums: 1 Day 18 h 28 m 56 sec
Reputation Power: 49

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.

March 5th, 2013, 12:57 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 53 m 18 sec
Reputation Power: 4140

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

March 5th, 2013, 01:21 PM

EEsterling

Contributing User

Join Date: Feb 2011

Posts: 97

Time spent in forums: 1 Day 18 h 28 m 56 sec
Reputation Power: 49

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...'

March 5th, 2013, 02:03 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 53 m 18 sec
Reputation Power: 4140

Quote:

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

March 5th, 2013, 03:37 PM

EEsterling

Contributing User

Join Date: Feb 2011

Posts: 97

Time spent in forums: 1 Day 18 h 28 m 56 sec
Reputation Power: 49

Okay. That work-around works.

March 5th, 2013, 03:44 PM

EEsterling

Contributing User

Join Date: Feb 2011

Posts: 97

Time spent in forums: 1 Day 18 h 28 m 56 sec
Reputation Power: 49

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')

March 5th, 2013, 03:45 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 53 m 18 sec
Reputation Power: 4140

sweet

Find the syntax error: select where IN subquery-union

Developer Shed Advertisers and Affiliates