Search condition mysql IF (name = name, LIMIT 1, LIMIT 5)

Question

Help create a search condition

SELECT *
FROM mlt_adr_city
WHERE name LIKE "Text%" 
AND region_id = 59
AND id <> 0 
IF (name = name, LIMIT 1, LIMIT 5)

Value field name can coincidence.
If the value is the same output a single line, or five.
Sorry i am bad english

[copied from comments:]

If the request without the condition, the names in which the records are repeated. For example WHERE name LIKE "City1" with the same name will return five rows but id they will be different.

But if there is no match then display five records.

Example Search LIKE "City1%" display records three City1, City1, City1.

Example 2. Search LIKE "City2%" display records three City2, City2, City2_en, City2_rect, City2_les.

Maybe you can give an example of the types of names you're matching, and the type of output you want. — tadman, Nov 30 '12 at 05:24
What are you trying to do that would be different from a where condition? — Ilion, Nov 30 '12 at 05:24
If the request without the condition, the names in which the records are repeated. For example WHERE name LIKE "City1" with the same name will return five rows but id they will be different. — Vayas, Nov 30 '12 at 05:31
Example Search LIKE "City1%" display records three City1, City1, City1 — Vayas, Nov 30 '12 at 05:35
Example 2. Search LIKE "City2%" display records three City2, City2, City2_en, City2_rect, City2_les — Vayas, Nov 30 '12 at 05:36
um, i'm still not entirely clear on what you're looking for. is this it: if there is an exact match for 'name', only show one row (even though more than one row might match exactly), but if there is no exact match then show 5 rows? — caitriona, Nov 30 '12 at 11:16

score 0 · Accepted Answer · edited May 23 '17 at 12:21

no, it's not possible to have conditional limit statement like you have in your question.

if you're using a stored procedure it is possible to have parameters or local variables as your limit value as described here:

How to make limit offset dynamic using only (My)SQL

and if you don't want to use a stored procedure, here's some sql that will return one row if there's an exact name match or else 5 rows if there's a partial name match:

SELECT * FROM mlt_adr_city
    WHERE
    name = 'Text'
    AND region_id = 59
    AND id <> 0
    LIMIT 1
UNION
SELECT * FROM mlt_adr_city
    WHERE
    name like 'Text%'
    AND region_id = 59
    AND id <> 0
    AND NOT EXISTS (SELECT 1 FROM mlt_adr_city WHERE name = 'Text' AND region_id = 59 AND id <> 0)
    LIMIT 5;

Sorry that I can not properly explain. I did not know about GROUP BY. Thank you all — Vayas, Dec 04 '12 at 07:31

score 0 · Answer 2 · answered Dec 04 '12 at 07:36

0

Example

SELECT * FROM mlt_adr_city WHERE name LIKE "Text%"  AND region_id = 59 AND id <> 0 GROUP BY name LIMIT 5

answered Dec 04 '12 at 07:36

Vayas

107
3
11

Search condition mysql IF (name = name, LIMIT 1, LIMIT 5)

2 Answers2