2

I have one query which is returning

name(text), total, created_at

And another query which is returning the same thing

name(text), total, created_at

The second query usually is returning more rows, but I only need the rows where the query1.created_at = query2.created_at

Example:
Query 1 returns:

test, 10, 2015-03-06
test, 12, 2015-03-07

Query 2 returns:

newtest, 14, 2015-03-05
newtest, 15, 2015-03-06
newtest, 9, 2015-03-07

The expected output:

test, 10, 2015-03-06
test, 12, 2015-03-07
newtest, 15, 2015-03-06
newtest, 9, 2015-03-07

QUERY 1:

SELECT
  co.name
  sum(c.total)
  c.created_at
FROM c_report c
INNER JOIN contact co on co.id = c.contact_id
GROUP BY co.name, c.created_at

QUERY 2:

SELECT
  po.name
  sum(p.total)
  p.created_at
FROM p_report p
INNER JOIN person po on po.id = p.person_id
GROUP BY po.name, p.created_at
  • INNER JOIN c.contact? You have a schema named c? Or typo? And pe.name is supposed to be po.name, right? – Erwin Brandstetter Mar 7 '15 at 22:17
3

I would use a CTE for the first query and reuse it for the second query:

WITH cte AS (
   SELECT co.name, sum(c.total) AS total, c.created_at
   FROM   c_report c
   JOIN   contact  co ON co.id = c.contact_id
   GROUP  BY co.name, c.created_at
   )
SELECT * FROM cte
UNION ALL
SELECT po.name, sum(p.total), p.created_at
FROM   p_report p
JOIN   person   po ON po.id = p.person_id
WHERE  EXISTS (SELECT 1 FROM cte WHERE created_at = p.created_at)
GROUP  BY po.name, p.created_at;

Be sure to use UNION ALL, not UNION. You don't want to remove duplicates, and it's faster that way.

0

I presume you mean you want only those rows from the second query where the date of the row exists in the table of the first query. You could modify your second query so it checks the exitence of the row in the first table. Use UNION to combine the queries (UNION ALL if there could be duplicates and you want to preserve them). For example:

SELECT
  co.name
  sum(c.total)
  c.created_at
  FROM c_report c
  INNER JOIN c.contact co on co.id = c.contact_id
  GROUP BY co.name, c.created_at
UNION
SELECT
  pe.name
  sum(c.total)
  p.created_at
  FROM p_report p
  INNER JOIN p.person po on po.id = p.person_id
  WHERE EXISTS (SELECT 1 FROM c_report cc WHERE cc.created_at = p.created_at)
  GROUP BY po.name, p.created_at;

More info from the documetation:
9.22. Subquery Expressions - EXISTS
7.4. Combining Queries

  • EXISTS cannot rely on c_report alone, all rows might not have survived the join to contact. And should be UNION ALL. – Erwin Brandstetter Mar 7 '15 at 22:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.