49059

UNION two tables in a subquery

Question:

I am trying to UNION two tables in a subquery, but apparently this is not allowed? A simplified version of my query is:

SELECT * FROM user, organization WHERE user.id NOT IN ( (SELECT distinct user_id FROM web_request) UNION (SELECT distinct user_id from user_milestone) ) AND user.organization_id = organization.id

My question is this: What is the best way to get around this shortcoming of MySQL?

Answer1:

SELECT u.*, o.* FROM `user` u INNER JOIN organization o ON u.organization_id = o.id WHERE u.id NOT IN( SELECT distinct user_id FROM web_request UNION SELECT distinct user_id from user_milestone )

Answer2:

Try this

SELECT * FROM USER u JOIN organization o ON u.organization_id = o.id WHERE u.uid NOT IN SELECT * ( ( SELECT DISTINCT user_id FROM web_request ) union ( SELECT DISTINCT user_id FROM user_milestone) ) AS t

Recommend

  • How to create a channelpath in BigQuery based on events?
  • Mysql optimization based on explain
  • facebook graph api retrieve wall photos
  • index name too long to rollback migration
  • Posting to Facebook page from Facebook application without login to facebook
  • SQLite PDO bindings not working?
  • Yii2 database session - store additional attributes and user information
  • Websocket across pages JavaScript
  • Selecting and check if user exist in table without foreach in laravel blade
  • current user id saved from _form or controller
  • Embedded or referenced relations
  • rails 5 carrierwave no route matches for image
  • RxJS: Setting default value for observable from another observable
  • Saving data to User model from Profile model Cakephp
  • Subquery returns more than 1 row
  • Flask-Restful error: “as_view” method not inherited
  • Using Select and where statement in Criteria
  • How do I write an item to a DynamoDb with the AWS DynamoDB DocumentClient?
  • Grouping by blank nodes
  • Query to get the Top 2 from each group
  • How to implement limit with Nhibernate and Sybase
  • How to Add Polymorphic Comments to Feed?
  • User messaging system
  • Laravel: Getting Session ID oddly truncates when using foreach
  • How to make a tree having multiple type of nodes and each node can have multiple child nodes in java
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • jquery mobile loadPage not working
  • How to get next/previous record number?
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • How do you join a server to an Active Directory (domain)?
  • How to stop GridView from loading again when I press back button?
  • Turn off referential integrity in Derby? is it possible?
  • How does Linux kernel interrupt the application?
  • Authorize attributes not working in MVC 4
  • Busy indicator not showing up in wpf window [duplicate]
  • Python/Django TangoWithDjango Models and Databases
  • Net Present Value in Excel for Grouped Recurring CF