mysql combine 2 queries from same table

Question

This is query 1:

SELECT distinct c.chem_gene, m.String_name, m.ScopeNote 
FROM mesher m INNER JOIN chem_base c 
ON (c.chem_name = m.String_name AND m.ScopeNote <> '' )
where match (c.chem_gene) against('ACTN3, MTHFR' in boolean mode)
group by c.chem_gene, c.chem_name;

which outputs 3 columns in rows like this:

'ACTN3', 'Estradiol', 'The 17-beta-isomer of estradiol...'

This is query 2 (taking the output from column 2, "Estradiol"):

SELECT String10 FROM mesher where String_name = "Estradiol" AND String10 <>'' LIMIT 1;

which outputs a single row in a single column:

'Estrogens'

How can I modify query 1 so that for each row returned the additional query is made against the result in the second column (i.e.'Estradiol') to produce this output:

 'ACTN3', 'Estradiol', 'The 17-beta-isomer of estradiol...', 'Estrogens'

Gordon Linoff · Accepted Answer · 2017-09-08 02:56:50Z

1

If I understand correctly, you can use a correlated subquery:

SELECT c.chem_gene, m.String_name, m.ScopeNote,
       (SELECT mm.String10
        FROM mesher mm
        WHERE mm.String_name = m.String_name AND mm.String10 <> ''
        LIMIT 1
       ) 
FROM mesher m INNER JOIN
     chem_base c 
     ON c.chem_name = m.String_name AND m.ScopeNote <> '' 
WHERE match(c.chem_gene) against( 'ACTN3, MTHFR' in boolean mode)
GROUP BY c.chem_gene, c.chem_name, m.ScopeNote ;

The select distinct is not necessary.

answered Sep 8, 2017 at 2:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql combine 2 queries from same table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related