How to not add values from Join

Question

SELECT ...

FROM [Rep_GroupNode] C                                    

left join                                                   
(SELECT ....     

if I'm using left join only for compare some thing due selecting how to not add(double) values from join ?

Answer 1

In the join, only select the distinct values of those columns you are joining on.

Example

SELECT  *
FROM    Table1
        LEFT JOIN (
          SELECT DISTINCT Col1, Col2
          FROM   Table2
        ) Table2 ON Table1.Col1 = Table2.Col1
                    AND Table1.Col2 = Table2.Col2

Answer 2

Then just write

SELECT C.* FROM [Rep_GroupNode] C LEFT JOIN (SELECT x, y, z FROM TableB B) ON ...
WHERE C.ReferenceID = B.ID

This should do what you would like to get... but also possible:

SELECT C.* FROM [Rep_GroupNode] C, [TableB] B WHERE C.ReferenceID = B.ID

Answer 3

You're encountering a many-to-many relationship.

Example:

Rep_GroupNode    MappingTable
-------------    ------------------------
id    name       id    rgn_id    rgn_type
1     a          1     1         type1
2     b          2     1         type2
3     c          3     2         type1

So a join on Rep_GroupNode.id = MappingTable.rgn_id returns two (2) records for Rep_GroupNode.id = 1

Some methods to handle this are mentioned in the other good answers, but for a definitive answer we'd need more information about what your data look like and exactly what information you'd like to project from it.

Here's an example (based on my sample data) of another method to avoid returning duplicate records:

   SELECT rgn.*
     FROM Rep_GroupNode rgn
LEFT JOIN MappingTable mt ON mt.rgn_id = rgn.id
      AND mt.rgn_type = 'type1'