CODEWITHSUNDEEP
sqljoin
MCB
MCB

Reputation: 2073

Why does this double JOIN work but this similar one not work?

This is for a Rails app, but I'm just posting the SQL.

So, this is my sanity test. It works, and returns two copies of the same model. And if I don't include the where multiple copies of multiple models are returned.

SELECT \"outlets\".* 
FROM \"outlets\" 
INNER JOIN \"comments\" 
ON \"comments\".\"commentable_id\" = \"outlets\".\"id\" 
AND \"comments\".\"commentable_type\" = 'Outlet'
INNER JOIN \"statistics\" 
ON \"statistics\".\"statable_id\" = \"outlets\".\"id\" 
AND \"statistics\".\"statable_type\" = 'Outlet' 
WHERE (\"outlets\".\"id\" = '1')

#=> [#<Outlet id: 1...>, #<Outlet id: 1...>]
# without the where I get back something like
# [...id: 1, id: 1, id: 5, id: 5, id: 5, id: 5 ]

I'm not sure how to write it so it only returns one value (without just using distinct). But that can be my next problem.

This is the SQL I'm trying to make work. I've also tried explicitly specifying INNER and reversing the order of the equalities:

SELECT \"followings\".* 
FROM \"followings\"
JOIN \"outlets\"
ON \"followings\".\"followable_id\" = \"outlets\".\"id\"
AND \"followings\".\"followable_type\" = 'Outlet'
JOIN \"people\"
ON \"followings\".\"followable_id\" = \"people\".\"id\"
AND \"followings\".\"followable_type\" = 'Person'
WHERE (\"followings\".\"user_id\" = '1')

#=> []
# Exact same result from removing the WHERE clause

There are records for each join. If I join on either table individually they both return the results I specially created to test my SQL. So:

SELECT \"followings\".* 
FROM \"followings\" 
INNER JOIN \"people\"
ON \"followings\".\"followable_id\" = \"people\".\"id\"
AND \"followings\".\"followable_type\" = 'Person' 
WHERE (\"followings\".\"user_id\" = '1')

#=> [<Following id: 2...>]
# And id: 1 for joining on outlets without people

The part that stumps me here is that the working version and the not-working version are essentially the same, but I don't see why it should work one place and not the other

EDIT:

Progress, changing the JOIN's for followings to LEFT OUTER JOINs will return the desired results. But then why is it working where outlets.id = commentable_id and = statable_id at the same time?

And, I just tried changing the outlets to LOJ's as well, and that returned a list of results that is approximately double the length of the outlets table itself in about the longest SQL call I've ever seen. So, while it's progress I don't think it's a general solution.

EDIT 2:

Thanks to testing things out with the accepted answer I realized that my choice of statistics was giving "misleading" results because each outlet has a statistic. When I changed to a better test table (e.g. followings) both JOIN's behaved similarly. And the UNION method returns the expected entries

Upvotes: 1

Views: 80

Answers (2)

ryan_godfrey
ryan_godfrey

Reputation: 26

This should get you what you want, I think. You can get the two lists separately and then combine them with UNION.

SELECT followings.*
FROM followings
JOIN outlets ON followings.followable_id = outlets.id
WHERE followable_type = 'Outlet'

UNION

SELECT followings.*
FROM followings
JOIN people ON followings.followable_id = people.id
WHERE followable_type = 'Person'

Upvotes: 1

Rahul
Rahul

Reputation: 77876

Your problem query, change it like below. Make the \"followings\".\"followable_type\" a OR condition (Have used IN operator). I have as well, removed the condition from JOIN to WHERE clause.

SELECT \"followings\".* 
FROM \"followings\"
JOIN \"outlets\"
ON \"followings\".\"followable_id\" = \"outlets\".\"id\"
JOIN \"people\"
ON \"followings\".\"followable_id\" = \"people\".\"id\"
WHERE (\"followings\".\"user_id\" = '1')
AND \"followings\".\"followable_type\" IN ('Outlet','Person')

You can as well say 

WHERE (\"followings\".\"user_id\" = '1')
AND (
\"followings\".\"followable_type\" = 'Outlet'
 OR 
\"followings\".\"followable_type\" = 'Person'
  )

EDIT:

Add the condition to JOIN itself, No need of a WHERE then

SELECT \"followings\".* 
FROM \"followings\"
JOIN \"outlets\"
ON \"followings\".\"followable_id\" = \"outlets\".\"id\"
AND \"followings\".\"followable_type\" IN ('Outlet','Person')
AND (\"followings\".\"user_id\" = '1')
JOIN \"people\"
ON \"followings\".\"followable_id\" = \"people\".\"id\"

Upvotes: 0

Related Questions