Find values ​​in SQL SELECT statements so that the concatenation of any two values ​​from each SELECT statement is below a certain maximum length?

Question

I have a difficult problem that I'm not quite sure how to handle.

I define two subsets of values ​​from the same table that match certain criteria.

operator 1:

SELECT value FROM Values WHERE category = a 

statement 2:

 SELECT value FROM Values WHERE category = b 

But I also have an additional restriction, which is that any value from operator 1 concatenated with any value of operator 2 must contain a string whose length must be equal to or less than the specified maximum length of the string.

What request can I write for this? Thanks in advance for your help.

Lothaire

Edit:

My data is as follows:

 +---+---------+------------+----------+ |id | routeId |category | value | +---+---------+------------+----------+ | 1 | 1 |origin | Paris | | 2 | 1 |destination | New York | | 3 | 2 |origin | Paris | | 4 | 2 |destination | Berlin | +------+--------------+---------------+ 

And I would like to get a list of routeId for routes where origin.destination is less than n characters.

Now, when I look more closely at this problem, I see that my initial approach was wrong, because I wrote that any value from the first select statement associated with any value from the second select statement must have a length under a certain restriction sign. In fact, the problem is more complicated, because routeId for the origin value and the target value should be the same for a pair of values, the concatenation of which should be no more than maximum.

Answer 1

Try:

 SELECT a.routeID FROM tbl a JOIN tbl b ON a.routeID = b.routeID AND b.category = 'destination' WHERE a.category = 'origin' AND CHAR_LENGTH(CONCAT(a.value, b.value)) <= 5 

Where tbl is the name of your table, and 5 is the maximum concatenation length of two statements. This compares the concatenation of each routeID source routeID with its destination value. If it is longer than five characters, then routeID filtered out.

---

Or perhaps this is what you want:

Comparing the concatenation of the origin value of each routeID value with routeID destination in the table:

 SELECT a.routeID FROM tbl a CROSS JOIN (SELECT value FROM tbl WHERE category='destination') b WHERE a.category = 'origin' GROUP BY a.routeID HAVING MAX(CHAR_LENGTH(CONCAT(a.value, b.value))) <= 5 

Answer 2

You can try this query:

 SELECT t1.value AS v1, t2.value AS v2, LEFT(CONCAT(t1.value, t2.value), 20) AS my_text FROM `values` t1, `values` t2 WHERE t1.category=a AND t2.category=b 

I used a maximum length of 20 characters, changing it as needed.

Since VALUES is a reserved word in MySQL , it must be enclosed in backlinks to avoid syntax errors.

Another request, after reading your comment:

 SELECT t1.value AS v1, t2.value AS v2, CONCAT(t1.value, t2.value) AS my_text FROM `values` t1, `values` t2 WHERE t1.category=a AND t2.category=b AND CHAR_LENGTH(CONCAT(t1.value, t2.value))<=20 

This query will return records whose concatenation of both values ​​is shorter or equal to 20 characters.

Documentation: LENGTH and CHAR_LENGTH