CODEWITHSUNDEEP
sqlpostgresqljoin
Tim Clemans
Tim Clemans

Reputation: 935

Joining two queries that have INNER JOINS in them

I'm trying to join these two SQL queries together. My data is at https://policevideorequests.cartodb.com/tables/seattle_police_govqa_audit_trails which has a Postgresql SQL API.

SELECT 
    t1.customer_id, t1.c,
    t2.customer_name, t2.customer_email, t2.customer_email_domain 
FROM 
    (SELECT 
         a.customer_id, count(a.customer_id) as c 
     FROM 
         (SELECT customer_id, reference_no 
          FROM seattle_police_govqa_audit_trails 
          WHERE customer_id NOT IN (5, 0, -1) 
          GROUP BY customer_id, reference_no) a 
     GROUP BY 
         a.customer_id 
     ORDER BY 
         count(a.customer_id) DESC) t1 
INNER JOIN 
    (SELECT DISTINCT 
         customer_id,
         INITCAP(LOWER(SUBSTRING(new_value FROM 'Dear&nbsp;(.*?):</div>'))) as customer_name,
         LOWER(SUBSTRING(new_value FROM 'login:<b>(.*?)</b>')) as customer_email, 
         LOWER(SUBSTRING(new_value FROM 'login:<b>.*?@(.*?)</b>')) as customer_email_domain 
     FROM 
         seattle_police_govqa_audit_trails 
     WHERE 
         SUBSTRING(new_value FROM 'Dear&nbsp;(.*?):</div>') IS NOT NULL) t2 ON t1.customer_id = t2.customer_id 
ORDER BY 
    t1.c DESC

SELECT DISTINCT 
    t1.new_value as requester_type, t2.customer_id 
FROM 
    (SELECT 
         reference_no, new_value 
     FROM 
         seattle_police_govqa_audit_trails 
     WHERE 
         action_desc = 154) t1
INNER JOIN 
    (SELECT 
         reference_no, customer_id 
     FROM 
         seattle_police_govqa_audit_trails 
     WHERE 
         customer_id NOT IN (0, -1, 5)) t2 ON t1.reference_no = t2.reference_no

My attempt at joining the two:

SELECT t1.customer_id,t3.requester_typer,t1.c,t2.customer_name,t2.customer_email,t2.customer_email_domain,t2.customer_email_domain_tld FROM (SELECT a.customer_id,count(a.customer_id) as c FROM (SELECT customer_id, reference_no FROM seattle_police_govqa_audit_trails WHERE customer_id NOT IN (5,0,-1) GROUP BY customer_id,reference_no) a GROUP BY a.customer_id ORDER BY count(a.customer_id) DESC) t1 
INNER JOIN (SELECT DISTINCT customer_id,INITCAP(LOWER(SUBSTRING(new_value FROM 'Dear&nbsp;(.*?):</div>'))) as customer_name,LOWER(SUBSTRING(new_value FROM 'login:<b>(.*?)</b>')) as customer_email, LOWER(SUBSTRING(new_value FROM 'login:<b>.*?@(.*?)</b>')) as customer_email_domain, LOWER(SUBSTRING(new_value FROM 'login:<b>.*?@.*?\.(.*?)</b>')) as customer_email_domain_tld FROM seattle_police_govqa_audit_trails WHERE SUBSTRING(new_value FROM 'Dear&nbsp;(.*?):</div>') IS NOT NULL) t2 
ON t1.customer_id = t2.customer_id ORDER BY t1.c DESC
INNER JOIN (SELECT DISTINCT t1.new_value as requester_type,t2.customer_id FROM (SELECT reference_no,new_value FROM seattle_police_govqa_audit_trails WHERE action_desc = 154) t1
INNER JOIN (SELECT reference_no,customer_id FROM seattle_police_govqa_audit_trails WHERE customer_id NOT IN (0,-1,5)) t2
ON t1.reference_no = t2.reference_no) as t3
ON t2.customer_id = t3.customer_id

I get the error "syntax error near INNER"

Upvotes: 1

Views: 30

Answers (2)

Luis Teijon
Luis Teijon

Reputation: 4899

Try this:

  SELECT t1.customer_id,t3.requester_typer,t1.c,t2.customer_name,t2.customer_email,t2.customer_email_domain,t2.customer_email_domain_tld FROM (SELECT a.customer_id,count(a.customer_id) as c FROM (SELECT customer_id, reference_no FROM seattle_police_govqa_audit_trails WHERE customer_id NOT IN (5,0,-1) GROUP BY customer_id,reference_no) a GROUP BY a.customer_id ORDER BY count(a.customer_id) DESC) t1 
    INNER JOIN (SELECT DISTINCT customer_id,INITCAP(LOWER(SUBSTRING(new_value FROM 'Dear&nbsp;(.*?):</div>'))) as customer_name,LOWER(SUBSTRING(new_value FROM 'login:<b>(.*?)</b>')) as customer_email, LOWER(SUBSTRING(new_value FROM 'login:<b>.*?@(.*?)</b>')) as customer_email_domain, LOWER(SUBSTRING(new_value FROM 'login:<b>.*?@.*?\.(.*?)</b>')) as customer_email_domain_tld FROM seattle_police_govqa_audit_trails WHERE SUBSTRING(new_value FROM 'Dear&nbsp;(.*?):</div>') IS NOT NULL) t2 
    ON t1.customer_id = t2.customer_id 
    INNER JOIN (SELECT DISTINCT t1.new_value as requester_type,t2.customer_id FROM (SELECT reference_no,new_value FROM seattle_police_govqa_audit_trails WHERE action_desc = 154) t1
    INNER JOIN (SELECT reference_no,customer_id FROM seattle_police_govqa_audit_trails WHERE customer_id NOT IN (0,-1,5)) t2
    ON t1.reference_no = t2.reference_no) as t3
    ON t2.customer_id = t3.customer_id
    ORDER BY t1.c DESC

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

The problem in your SQL query is that you tried to keep ORDER BY in the middle. The ORDER BY clause must be moved all the way to the back of the query, because ordering is applied to the entire query, not to its parts.

Upvotes: 3

Related Questions