Is there a way to make a strict join?

Question

I am a beginner in sql, and i am facing this problem.

I have to make a join between two tables (Masse and LX03).

My join is made between two columns with different names : LX03.Emplacement and Masse.IDMasse

I excluded some values from my select, to shorten my code.

SELECT DISTINCT 
    Masse.IDMasse AS IDMasse,   
    Masse.Capacité_stock_sol AS Capacité_stock_sol, 
        ...
    LX03.Emplacement AS Emplacement,    
    LX03.Article AS Article,    
    ...
FROM 
    LX03 INNER JOIN Masse ON LX03.Emplacement = Masse.IDMasse

I would like my query to match those two columns exactly, but not to be case sensitive. For example : m14 = M14 m1-4 = m1-4

When i run my query though, it returns me results where those two values does not exactly matches, like : LX03.Emplacement = M1-4 and Masse.IDMasse = M14

I initially tried to exclude all values that contained punctuation, but i need those too.

I would like to know if there is a way to ask for values strictly similar, but does not make it case sensitive?

I couldn't find much help on the internet, but maybe since i am a beginner i didn't use the right terms? If so, please refer me to some website that explains it - i want to learn from my mistakes !

Thanks for your help

EDIT for clarification: At the moment, these are the two values matched by my query: M14 = M1-4 But i don't want values with "-" to be matched to values without it.

Your question contradicts itself 'I would like my query to match those two columns exactly,' and 'I would like to know if there is a way to ask for values strictly similar' which do you want. AND are you using and ide? — P.Salmon, Jul 31 '19 at 08:53
An equality operator in mysql is exactly that EXCEPT for case sensitivity which is a an option you can set and if you don't want to set case sensitivity you could use UPPER or LOWER in you query to make both sides the same case. — P.Salmon, Jul 31 '19 at 08:55
@P.Salmon I am using Windev as an IDE - not fairly common. I would like to match the columns without punctuation to the columns without punctuation too - but at the moment, columns without and with punctuation are matched. Does that clarify my question? (sorry abt bad english) — Samuel, Jul 31 '19 at 08:57
Not really punctuation would not make a difference (as far as mysql is concerned it's just a string element(s)). IF it does then there must be a setting in windev (and I cannot imagine that's the case) Can you add sample data as text to the question please. — P.Salmon, Jul 31 '19 at 08:59
I added an image of a few of my results - as you can see, some values with and without "-" are matched. — Samuel, Jul 31 '19 at 09:02
for the case insensivity: use [`UPPER()`](https://www.w3resource.com/mysql/string-functions/mysql-upper-function.php) or `LOWER()` to make both sides the same case. — inetphantom, Jul 31 '19 at 09:02
Thanks both of you! the case insensitivity works fine now ! how to separate the values with from the values without "-" now? Thanks again! — Samuel, Jul 31 '19 at 09:08
'I added an image of a few of my results - as you can see, some values with and without "-" are matched' - not possible in mysql assuming the datatypes are similar what are the datatypes of these 2 items?. I suggest you run your query in mysql command line to take windev out of the mix for now. — P.Salmon, Jul 31 '19 at 09:08
unfortunately, my copany does not allow me to access the mysql command line. I think, too, windev is the problem here. Unfortunately, i have to work around this problem — Samuel, Jul 31 '19 at 09:13
I have added a windev tag to the question in the hope that someone familiar with this product may be able to help. But if that's the tool available to you I would have expected someone internally would be a quicker option. — P.Salmon, Jul 31 '19 at 09:19
as you asked - "how to separate the values with from the values without '-' now?" - do you want to consider M12 and M1-2 as same value? — mkRabbani, Jul 31 '19 at 09:27
@mkRabbani no , i dont want those to be the same values. those are matched by my query though — Samuel, Jul 31 '19 at 09:28
to make the whole issue more clear, I suggest you edit your question and add both your input table structure with some sample data, then the query you are trying with and finally your expected output considering your given sample data, This would help. — mkRabbani, Jul 31 '19 at 09:33

score 0 · Answer 1 · answered Jul 31 '19 at 09:30

presuming you only need to ignore - in the value column you can ignore the case then use them in join clause -

SELECT DISTINCT 
Masse.IDMasse AS IDMasse,   
Masse.Capacité_stock_sol AS Capacité_stock_sol, 
    ...
LX03.Emplacement AS Emplacement,    
LX03.Article AS Article,    
...
FROM LX03 
INNER JOIN Masse ON REPLCAE(UPPER(LX03.Emplacement), '-', '') = REPLCAE(UPPER(Masse.IDMasse), '-', '')

score -1 · Answer 2 · answered Jul 31 '19 at 09:01

-1

In regards to this :

When i run my query though, it returns me results where those two values does not exactly matches, like : LX03.Emplacement = M1-4 and Masse.IDMasse = M14 I initially tried to exclude all values that contained punctuation, but i need those too.

You can try to compare lenght (after join)

answered Jul 31 '19 at 09:01

Kuba Do

155
9

I added this clause : WHERE CHAR_LENGTH(LX03.Emplacement) = CHAR_LENGTH(Masse.IDMasse) But my query does not return any results. Maybe i am using Char_Lenght poorly? – Samuel Jul 31 '19 at 09:04

Is there a way to make a strict join?

2 Answers2