Mysql – combine two select queries

MySQLquery

I have two Mysql select queries:

select TTS.SSML, TTS_TAG.SORTID, TTS.VoiceId , TTS_TAG.TAGID FROM TTS 
left join TTS_TAG
on TTS_TAG.TTSID = TTS.TTSID 
where TTS.MID = 15

select max(TTS_TAG.SORTID) as maxsort from TTS_TAG
where TTS_TAG.TAGID = 1

I wish to combine both statements into one select statement, with the aggregate value added as a field in resulting output record?

The second aggregate select statement retrieves the maximum 'sort' for all records with the value 1 – if this second select yielded '3', then my desired record set would be:

# SSML, SORTID, VoiceId, TAGID, maxsort
'ssml data', '1', 'Geraint', '4', '3'

Best Answer

Thanks Lennart! A second sub select query within the first worked.

select TTS.SSML, TTS_TAG.SORTID, TTS.VoiceId , TTS_TAG.TAGID, 
(select max(TTS_TAG.SORTID) from TTS_TAG
where TTS_TAG.TAGID = 1) as maxsort
FROM TTS 
left join TTS_TAG
on TTS_TAG.TTSID = TTS.TTSID 
where TTS.MID = 15

Related Solutions

Mysql – Subselect dependent on first select value

You can use 2 subqueries, one joining with the contact_id and another joining with the alt_contact_id. Then UNION them and ORDER BY:

SELECT cont.*, cred.*, 'main' AS contact_type
FROM e_contacts AS cont
  JOIN e_credentials AS cred
    ON cred.contact_id = cont.contact_id
WHERE cont.contact_id = 1

UNION ALL 

SELECT cont.*, cred.*, 'alternate'
FROM e_contacts AS cont
  JOIN e_credentials AS cred
    ON cred.contact_id = cont.alt_contact_id
WHERE cont.contact_id = 1

ORDER BY contact_id ;

If you want this for all contacts, just remove the two WHERE clauses.

Mysql – ORA-00937 when calculating a sub-value in an aggregate select statement

Your additional calculated value:

(select count(t1.num) from T t1)

Is a scalar subquery, which is a dynamic rather than static expression. As such it's treated the same as a column as far as the aggregate is concerned and needs to be included in the group by clause to avoid the ORA-00937: not a single-group group function error

However, oracle does not allow subqueries as part of the group by clause and trying to include the scalar subquery and/or the whole case statement:

group by (case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

just results in an ORA-22818: subquery expressions not allowed here error.

The only ways around this are to either convert your scalar subquery to an aggregate value like so:

max(case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

(case max((select count(*) cnt from t t1)) when 0 then 1 else 0 end)

or rewrite your query to move the unaggregated scalar subquery out of the aggregated query:

select (case (select count(*) cnt from t t1) when 0 then 1 else 0 end) * sum
  from (select sum(t3.num) sum from t t3) t2;

or precompute your scalar subquery so it can be used in the group by clause:

select case t1.cnt when 0 then 1 else 0 end * sum(t2.num)
  from t t2
     , (select count(*) cnt from t) t1
 group by case t1.cnt when 0 then 1 else 0 end

Best Answer

Related Solutions

Mysql – Subselect dependent on first select value

Mysql – ORA-00937 when calculating a sub-value in an aggregate select statement

Related Question