0

I managed to patch together this frankenstein query but I'm getting the error Unknown column 'r.user_id' in 'where clause'

Can someone please help me with this? I gave the last portion an alias, but the nested subquery is unable to recognize it. Googling tells me to use a group by but I can't seem to figure out where.

Thanks

SELECT 
r.id, r.state,

count((SELECT dog.ip_address

FROM

(SELECT DISTINCT ip_address COLLATE utf8_unicode_ci AS ip_address
FROM previous_state_transitions
WHERE user_id = r.user_id

UNION

SELECT DISTINCT ip_address COLLATE utf8_unicode_ci AS ip_address
FROM previous_state_transitions
WHERE state_machineable_id = r.user_id
AND state_machineable_type = "User"

UNION 

SELECT DISTINCT ip_address
FROM reviews 
WHERE user_id = r.user_id) AS dog

INNER JOIN

(SELECT DISTINCT ip_address COLLATE utf8_unicode_ci AS ip_address
FROM previous_state_transitions
WHERE user_id IN (SELECT user_id FROM company_user_roles WHERE company_id = r.company_id)

UNION

SELECT DISTINCT ip_address COLLATE utf8_unicode_ci AS ip_address
FROM previous_state_transitions
WHERE state_machineable_id = (SELECT user_id FROM company_user_roles WHERE company_id = r.company_id)
AND state_machineable_type = "User"

UNION 

SELECT DISTINCT ip_address
FROM reviews 
WHERE user_id = (SELECT user_id FROM company_user_roles WHERE company_id = r.company_id)) AS cat

ON dog.ip_address = cat.ip_address)) AS ip_count_in_common

FROM reviews AS r
WHERE r.created_at > '2016-10-13'
ORDER BY 1 desc
Joseph Noirre
  • 387
  • 4
  • 20

1 Answers1

0

You need to include a GROUP BY clause before the Order By clause that includes the r.id and r.state.

For example a very simple query would be...,

SELECT id, name, count(*) from maker GROUP BY id, name
Branhap
  • 21
  • 1
  • I'm still getting the same error. I'm not trying to count the number of reviews or anything. Just how many IPs the user and company have in common. – Joseph Noirre Oct 14 '16 at 20:28