Search code examples
mysqlmany-to-many

LIKE function on MySQL not picking up similar words

I am currently using db-fiddle.com to practice my SQL code.

I have two tables.

'Customers':

Customers_id PracticeName Location PracticeType Subref
1 Hermitage Vets Essex Farm P030022
2 West End Vets Edinburgh Companion P030023

'Samples':

Samples_id Subref SampleType det FAM VIC Gel_result
1 P030022_1 SWAB MHYPCR 38.72 35.00 null
2 P030022_2 SWAB MHYPCR 34.23 30.05 null
3 P030022_3 SWAB MHYPCR 34.00 29.99 null
4 P030022_4 SWAB MHYPCR 30.00 37.10 null

(There are more subref samples and more columns in Samples but I didn't want to clutter the page.)

There can be multiple samples as part of one subref that is why I made them two tables with the underscore to define results per sample.

I want to be able to see all the samples that are part of the Subref next to PracticeName. So the end result is the Samples table but with the PracticeName column attached.

Sorry if this is not the best way to format things but I am a very new beginner.

I tried both these codes, which apparently executed but nothing showed up in the results:

SELECT * FROM Customers, Samples WHERE Customers.Subref LIKE (Samples.Subref + '%');

SELECT * FROM Customers, Samples WHERE Customers.Subref LIKE concat (Samples.Subref, '%');

I have also tried creating another table to do many-to-many queries but again it executed with no results :

  Customers_id INT UNSIGNED NOT NULL
  , Samples_id SMALLINT UNSIGNED NOT NULL
  , PRIMARY KEY pk_Customers2Samples (Customers_id, Samples_id)
);

WHERE Customers.Subref like 'Samples.Subref%';

I am not sure what I am doing wrong.

Any advice appreciated, please be nice.

Solution

  • Looking to yourdata sample .. due the repsence of _n at the end yous houdl use the inverse (match for string from customers + % like the string in samples)

    SELECT * 
FROM Customers
INNER JOIN Samples ON  Samples.Subref LIKE concat (Customers.Subref, '%')

    the string for Sample in your data containg more chats respect the value in customers then can't macth .. the opposite should work