Query doesn't seem to yield the correct results

Question

I have a table with three fields, looks like this...........

tblValues

NameFrom          NameTo            Difference
abbbb             arrrr             16
acccc             agggg             20
adddd             annnn             17

My query looks like this...

Select 'From' = tblValues.NameFrom, 
       'To' = tblValues.NameTo, 
       TblValues.Difference, 
       'Other' = x1.Difference
from   tblValues
  LEFT JOIN tblValues X1 
    ON tblValues.NameFrom = X1.NameTo 
   AND tblValues.NameTo = X1.NameFrom 
WHERE  tblValues.NameFrom Like '%a%' OR tblValues.NameTo Like '%a%'
ORDER  BY tblValues.NameFrom, tblValues.NameTo

I let user search a text value in this case 'a'. I have about 30000 values that are not edited/updated by anyone. They've been entered into this table and have been as they are.

The data looks like this....

From       To         Difference       Other
abbbb      arrrr      16               16
....       ....
'Same for all the values - or at least that's what it should be!

The problem i have is that when I run this query there are a few records where OTHER = NULL - even though Difference has a value. Any idea why?

I know this can simply be done another way - but this query was written by someone else and while I don't want to change it - i wanna know why it does not yield the valid data (NULL values) — BobSki, Jan 09 '17 at 16:05

score 2 · Accepted Answer · answered Jan 09 '17 at 16:14

You are picking the value of other with this line

LEFT JOIN tblValues X1 ON tblValues.NameFrom = X1.NameTo

And you display it with this:

'Other' = x1.Difference

A LEFT JOIN means: join this table. If there is no connected record return with NULL. If there is one (or if there are more than one), return with all of them.

For your query, where you are joining the same table as the source table, that means:

Whenever there is no record found, where tblValues.NameFrom = X1.NameTo this field other will stay NULL... Why this happens nobody can tell you from outside...

score 1 · Answer 2 · edited May 23 '17 at 12:24

1

It looks like you want to change LEFT JOIN to JOIN see a graphical explanation of SQL joins here: https://stackoverflow.com/a/406333/2054629.

Basically left join means

all rows from table A
matching rows of table B if found, else NULL

edited May 23 '17 at 12:24

Community

1
1

answered Jan 09 '17 at 16:12

Guig

9,891
7
64
126

score 1 · Answer 3 · answered Jan 09 '17 at 16:14

Your results from the X1 table are NULLs if there are no corresponding NameTo values for Table tblValues NameFrom. The Left Join includes all records from tblValues even if there are no corresponding matches in X1. Use an INNER JOIN:

Select 'From' = tblValues.NameFrom, 'To' = tblValues.NameTo, TblValues.Difference, 'Other' = x1.Difference
from tblValues
INNER JOIN tblValues X1 ON tblValues.NameFrom = X1.NameTo 
AND tblValues.NameTo = X1.NameFrom 
WHERE tblValues.NameFrom Like '%a%' OR tblValues.NameTo Like '%a%'
ORDER BY tblValues.NameFrom, tblValues.NameTo

Query doesn't seem to yield the correct results

3 Answers3