How to merge two queries in mysql

Question

I've tried solutions shown in here, here, here and here

But no success, here are the two queries I'm tring to convert into one:

q1

SELECT c.corpus_initial 
FROM 
(SELECT SUBSTRING_INDEX(corpus, '_', 1) AS corpus_initial 
FROM corpus_info WHERE corpus 
 IN 
(SELECT corpus FROM corpus_alignments)) c GROUP BY c.corpus_initial

q2

SELECT c.title_initial 
FROM 
(SELECT SUBSTRING_INDEX(title, ' (', 1) AS title_initial
FROM corpus_info WHERE corpus 
IN 
(SELECT corpus FROM corpus_alignments)) c GROUP BY c.title_initial

UPDATE

some data: the table corpus_info has many fields but the two important ones are corpus and title, first I filter the results comparing to table corpus_alignments, so if the data is in this later table then I take out the last part of the results and marge them....

table corpus_info

|    corpus   |             title             |
_______________________________________________ 
| btdeucat_de | BancTrad Deutch-Catalan (de)  |
_______________________________________________
| btdeucat_ca | BancTrad Deutch-Catalan (ca)  |
_______________________________________________
| btengspa_en | BancTrad English-Spanish (en) |
_______________________________________________
| btengspa_sp | BancTrad English-Spanish (sp) |
_______________________________________________

So from column corpus I get: btdeucat and btengspa without the _xx and not duplicated and from column title I get: BancTrad Deutch-Catalan and BancTrad English-Spanish without the (xx) and not duplicated

you need to show some sample data and expected output. There are many possible ways to "merge" a query, as shown already by the two alternatives in the comments above. We don't know what you're actually trying to achieve, so we can't offer any definitive advice. — ADyson, Sep 04 '17 at 14:38
Union gives me results but are all inside the same column so all displayed, I need to display the results separately, like: `echo "";'' — Andrés Chandía, Sep 04 '17 at 14:46
Maybe you should start by fixing q1 and q2. There is no reason to be using nested sub-selects in either query - they may be giving you the result you expect but they don't scale and can be a performance nightmare. — symcbean, Sep 04 '17 at 15:39

score 0 · Answer 1 · answered Sep 04 '17 at 15:29

You can first replace the 2 special character into some other special chara nd then select the data based on that char, If your table dones not have the 3rd char in the table. Something like this -

SELECT c.corpus_initial 
FROM (SELECT SUBSTRING_INDEX(REPLACE(REPLACE(corpus,'_','@'),'(','@'), '_', 1) AS corpus_initial 
      FROM corpus_info 
      WHERE corpus IN (SELECT corpus 
                       FROM corpus_alignments)
     ) c 
GROUP BY c.corpus_initial

Hope this helps.

How to merge two queries in mysql

1 Answers1