Getting Distinct Keys with Inner Join-CodePudding

I have the below code attempting to get distinct nm.key entries, along with the other specified columns

SELECT DISTINCT nm.key, nt.nt, pd.r, pd.b, pd.s
FROM nm
    INNER JOIN nt ON 
    nm.nkey==nt.nkey

    INNER JOIN pd ON 
    pd.key==nm.key


   
WHERE nt.nt RLIKE "pain"

It is not returning distinct nm.key when I run this. Does anyone know why? Also, would anyone know why the "WHERE" statement is not working

CodePudding user response：

I have the below code attempting to get distinct nm.key entries, along with the other specified columns

SELECT DISTINCT gives you distinct rows. If your query is returning more than one row with the same nm.key, then it must be that they differ in one or more of the other columns.

And that belies a conceptual or design problem. Evidently, nm.key is not sufficient to uniquely identify values for all the other selected columns, so which values of the other columns do you want?

Also, would anyone know why the "WHERE" statement is not working

For one thing, you're using the wrong quotes. Char and varchar literals are expressed in single quotes in standard SQL:

WHERE nt.nt RLIKE 'pain'

But also, RLIKE performs a regular-expression match (at least in MySQL, to which it seems to be idiosyncratic). Your pattern is a simple string. You can use that as a regex, but it would be equivalent and clearer to just use =, like so:

WHERE nt.nt = 'pain'