12

How can I combine those two queries into one?

1) This finds the japanese sign for dog (犬):

SELECT japanese 
  FROM edict 
 WHERE english LIKE 'dog' 
 LIMIT 1;

2) This finds all japanese words with the sign for 'dog' (犬) in it:

SELECT japanese 
  FROM edict 
 WHERE japanese LIKE '%犬%';

3) I am having trouble combining those two into one, because this doesn't work?!

SELECT japanese 
FROM edict 
WHERE japanese
LIKE CONCAT('%',
    SELECT japanese FROM edict WHERE english LIKE 'dog' LIMIT 1,'%'
);
pirho
  • 11,565
  • 12
  • 43
  • 70
ajo
  • 1,045
  • 4
  • 15
  • 30

2 Answers2

28

Parenthesises are important, therefore, try this :

SELECT japanese
FROM edict
WHERE japanese LIKE CONCAT('%', 
                           (SELECT japanese FROM edict WHERE english LIKE 'dog' LIMIT 1), 
                           '%');

It might have been good to tell us what error you received, though.

Vincent Savard
  • 34,979
  • 10
  • 68
  • 73
  • 1
    Welcome! As I can't comment other posts yet, I'll just say here that using LIMIT in a subquery is usually not the best way to do things, but this query will most likely be faster due to the fact that you only have to look up in the index instead of joining two tables. – Vincent Savard Oct 23 '10 at 18:20
  • also applicable for Excel MS Query with ? parameter in combination with like – holgrich Jun 06 '16 at 09:54
7

Use:

SELECT a.japanese 
  FROM EDICT a
  JOIN EDICT b ON b.japanese = a.japanese
 WHERE b.english LIKE 'dog'

I don't recommend the use of LIMIT, but if you really need it for this, use:

SELECT a.japanese 
  FROM EDICT a
  JOIN (SELECT t.japanese
          FROM EDICT t
         WHERE t.english LIKE 'dog'
         LIMIT 1) b ON b.japanese = a.japanese
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • I've gone for Fayden's answer. BTW: why don't you recommend the use of LIMIT? – ajo Oct 23 '10 at 18:14
  • @ajo: `LIMIT` is an artificial means of ensuring you get one record. Better to know why you have multiple results, and deal with such cases. – OMG Ponies Oct 23 '10 at 18:18
  • Could you do this with ... b.japanese LIKE CONCAT('%',a.japanese,'%') ...? – ajo Oct 23 '10 at 22:12