CODEWITHSUNDEEP
sqlsql-serverleft-join
BobSki
BobSki

Reputation: 1552

Query doesn't seem to yield the correct results

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?

Upvotes: 0

Views: 107

Answers (3)

cloudsafe
cloudsafe

Reputation: 2506

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

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

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...

Upvotes: 2

Guig
Guig

Reputation: 10429

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

Upvotes: 1

Related Questions