CODEWITHSUNDEEP
mysql
haz
haz

Reputation: 780

mysql combine 2 queries from same table

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'

Upvotes: 1

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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.

Upvotes: 1

Related Questions