MySQL LIKE Using % Symbol form join

Question

I have 2 simple table:

Table a:

+====+======+
| ID | Name |
+====+======+
| 1  | Alex |
| 2  | Steve|
+===========+

Table b:

+======+======+
| ID_B |Number|
+======+======+
| 1-20 | 20   |
| 1-21 | 40   |
+=============+

I tied to use this syntax:

SELECT `Name`,sum(`Number`) From `a` Join `b` ON `a`.`ID` LIKE `b`.`ID_B`+'%'

I don't know all about Mysql, am a newbie. I want to join table a to table b, and in table b Number's is automatically count all WHERE ID table a is Same like b .ID_B %

Finally my answer is "Alex have 60 Number".

What is `ID_B`, and why isn't it a foreign key? Or I guess rather, you need a foreign key. — Rogue, Jun 10 '17 at 07:01
@Rogue, that just example, i want to marge all table WHERE same ID — Abdul Aziz Al Basyir, Jun 10 '17 at 07:04
So Steve is also 60 because it is in 1 upto 20? Or does 1-20.meen only 1 and 20 match? — Thallius, Jun 10 '17 at 07:10
The information in `ID_B` should be separate columns basically. E.g. `1-21` would become columns of `1` and `21`, and you'd put what is called a "Foreign Key Constraint" between `a#ID` and `b#ID` (assuming the `1` is in a new column named `ID`, I'm not sure of the purpoes of the second number). Lastly you'd do a SUM function in the select (on `b#Number`) and a GROUP BY for the id. — Rogue, Jun 10 '17 at 07:12
That table can't to use that?, i have searching at google, and i find solution but it not work for me, https://stackoverflow.com/questions/1386166/how-to-use-a-like-with-a-join-in-sql — Abdul Aziz Al Basyir, Jun 10 '17 at 07:14
Because it's silly to do a `LIKE` on a join vs `a.id = b.id`, both speed and code-wise. The `ON` is merely a conditional clause which specifies what makes a row in table `a`, and a row in table `b` "match". — Rogue, Jun 10 '17 at 07:17

trincot · Accepted Answer · 2017-06-10T07:38:22.837

You need to:

swap the positions of a.id and bid_b in the like operator (the longer of the two values should be on the left so its remaining part matches the wildcard on the right)
convert the a.id to string and concatenate it to %, which you can do with concat. Even though you have a reference, the + operator does not concatenate strings in MySql
also better add a group by

It would look like this:

select   name, sum(number) 
from     a 
join     b 
      on b.b_id like concat(a.id, '%') 
group by name

Depending on your case, you might want to add the hyphen before the %:

      on b.b_id like concat(a.id, '-%')

score 1 · Answer 2 · answered Jun 10 '17 at 07:13

I have this:

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE a (
  ID INT,
  NAME VARCHAR(25)
);

INSERT INTO a VALUES
(1, 'Alex'),
(2, 'Steeve');

CREATE TABLE b (
  ID_B VARCHAR(25),
  Number INT
);

INSERT INTO b VALUES
('1-20', 20),
('1-21', 40);

Query 1:

SELECT a.NAME, t.* FROM a LEFT JOIN (SELECT LEFT(ID_B, INSTR(ID_B, '-') - 1) AS ID, SUM(Number) 
FROM b
GROUP BY LEFT(ID_B, INSTR(ID_B, '-') + 1)) AS t
ON (a.ID = t.ID)

Results:

|   NAME |     ID | SUM(Number) |
|--------|--------|-------------|
|   Alex |      1 |          60 |
| Steeve | (null) |      (null) |

MySQL LIKE Using % Symbol form join

2 Answers2