CODEWITHSUNDEEP
sqljoinunion
Farley Knight
Farley Knight

Reputation: 1793

How to combine these two complicated queries into one query without union?

(SELECT posts.id FROM posts 
INNER JOIN discussions ON discussions.post_id = posts.id 
INNER JOIN companies ON discussions.company_id = companies.id 
INNER JOIN subscriptions ON subscriptions.subscribable_id = companies.id AND subscriptions.subscribable_type = 'Company' 
INNER JOIN users ON subscriptions.user_id = users.id WHERE users.id = 6)

UNION 

(SELECT posts.id FROM posts 
INNER JOIN users users_2 ON posts.analyst_id = users_2.id 
INNER JOIN subscriptions ON subscriptions.subscribable_id = users_2.id AND subscriptions.subscribable_type = 'User'
INNER JOIN users ON subscriptions.user_id = users.id WHERE users.id = 6)

It should be obvious that the last join is the same in both queries.. Just not sure how to "or" together joins.

Upvotes: 2

Views: 698

Answers (3)

bombnomnom
bombnomnom

Reputation: 61

I think there are differences I'm overlooking but if you just want the post if it's in the first or second without using a UNION then:

SELECT posts.id FROM posts
where posts.id IN 
(
SELECT posts.id FROM posts
INNER JOIN discussions ON discussions.post_id = posts.id 
INNER JOIN companies ON discussions.company_id = companies.id 
INNER JOIN subscriptions ON subscriptions.subscribable_id = companies.id AND subscriptions.subscribable_type = 'Company' 
INNER JOIN users ON subscriptions.user_id = users.id WHERE users.id = 6
)
or
posts.id IN 
(
SELECT posts.id FROM posts 
INNER JOIN users users_2 ON posts.analyst_id = users_2.id 
INNER JOIN subscriptions ON subscriptions.subscribable_id = users_2.id AND subscriptions.subscribable_type = 'User'
INNER JOIN users ON subscriptions.user_id = users.id WHERE users.id = 6
)

Upvotes: 0

Andriy M
Andriy M

Reputation: 77677

Some joins seem redundant:

  • discussions could be joined to subscriptions directly on the company_id column;

  • posts could be joined to subscriptions directly on the analyst_id column;

  • the last join to users in either SELECT is unnecessary as no data is retrieved from that table and the filter (users.id = 6) could be re-applied to subscriptions.user_id.

So, I would probably rewrite the query like this:

SELECT p.id
FROM posts p
  INNER JOIN discussions d ON d.post_id = p.id
  INNER JOIN subscription s
    ON s.subscribable_type = 'Company' AND s.subscribable_id = d.company_id
    OR s.subscribable_type = 'User'    AND s.subscribable_id = p.analyst_id
WHERE s.user_id = 6

Upvotes: 2

user596075
user596075

Reputation:

This is untested, but give this a try. Let me know if it works.

select posts.id
from posts
inner join discussions
on discussions.post_id = posts.id
inner join companies
on discussions.company_id = companies.id
inner join subscriptions
on subscriptions.subcribable_id = companies.id
inner join users
on subscriptions.user_id = users.id
or users.id = posts.analysis_id
where subscriptions.subscribable_type in ('Company', 'User')
and users.id = 6

Upvotes: 1

Related Questions