0

I have these two queries that I need to make into a single query. Keyfield1 and TPOLNO should be the join fields. How would I go about making this a single query?

SELECT TPOLNO, SUM(TTSAMT) AS SUM FROM PFPOSTR410 WHERE 
((TTRNYY=2012 AND TTRNMM=3 AND TTRNDD>=27) OR (TTRNYY=2012 AND TTRNMM>3) OR 
(TTRNYY=2013 AND TTRNMM<=2) OR (TTRNYY=2013 AND TTRNMM=3 AND TTRNDD<=27)) 
GROUP BY TPOLNO HAVING SUM(TTSAMT)>=5000 ORDER BY TPOLNO ASC

SELECT KEYFIELD1, KEYFROBJ FROM CMRELATN WHERE RELROLETC=8

Thanks in advance for any direction!

  • Josh
ResourceReaper
  • 555
  • 2
  • 10
  • 27

1 Answers1

-1

If i understand your questions, you need to do a simple inner join of the 2 tables:

SELECT TPOLNO, SUM(TTSAMT) AS SUM, KEYFIELD1, KEYFROBJ
FROM PFPOSTR410, CMRELATN 
WHERE 
    ((TTRNYY=2012 AND TTRNMM=3 AND TTRNDD>=27) OR (TTRNYY=2012 AND TTRNMM>3) OR 
(TTRNYY=2013 AND TTRNMM<=2) OR (TTRNYY=2013 AND TTRNMM=3 AND TTRNDD<=27))
    AND KEYFIELD1=TPOLNO
    AND RELROLETC=8
GROUP BY TPOLNO, KEYFIELD1, KEYFROBJ
HAVING SUM(TTSAMT)>=5000
ORDER BY TPOLNO ASC
gtsouk
  • 5,208
  • 1
  • 28
  • 35
  • yup that is what I needed pretty much. I edited the statement to remove the KEYFIELD1 from the select clause and added it to the group by clause. Works like a charm! Thank you! – ResourceReaper May 06 '13 at 14:41
  • Never sugest using implicit joins, they are a SQL antipattern. – HLGEM May 06 '13 at 15:09
  • And group by shoudl always include all the nonaggregate fields even though mysql allows you to do the wrong thing like this. In general this is a very poor answer using two SQL antipattern techniques. We are not supposed to be teaching the wrong way to do things. – HLGEM May 06 '13 at 15:12
  • You are right, i updated my answer to include the rest of the fields in the group by. – gtsouk May 06 '13 at 15:52
  • What do you mean "SQL antipattent"? – gtsouk May 06 '13 at 16:00
  • @HLGEM - edited the above post for what I changed it to be (now using an inner join) – ResourceReaper May 06 '13 at 16:03
  • @gtsouk - read this: http://stackoverflow.com/questions/346659/what-are-the-most-common-sql-anti-patterns It might help a bit. – ResourceReaper May 06 '13 at 16:06
  • @ResourceReaper this question is "closed as not constructive". The answer about explicit vs implicit joins got 13 votes and had no reference to support the claim. I wouldn't say it must be avoided just because of that. – gtsouk May 06 '13 at 16:14