SQL Filtering rows with no duplicate value

Question

Hi so I'm new to SQL and I'm trying to find a way in which I can obtain only the rows that have values that are not duplicate to each other in a specific column of table.

For example the Table below is called T1 and contains:

ID|Branch ID
1     444
2     333
3     444
4     111
5     555
6     333

The result I want will be

ID|Branch ID
4     111
5     555

So only showing non duplicate rows

Edit: I want to apply this to a large relational code. Here is a snippet of where I want it to be added

FROM dbo.LogicalLine
          INNER JOIN dbo.Page ON dbo.LogicalLine.page_id = dbo.Page.id
          INNER JOIN dbo.Branch ON dbo.LogicalLine.branch_id = dbo.Branch.id

The table LogicalLine will have a column called branch_id containing duplicate id values. I wish to filter those out showing only the non-duplicate branch_id like above example then INNER JOIN the Branch table into the LogicalLine which I have done.

Added -Full Code here:

SELECT  

(SELECT name
 FROM ParentDevice
 WHERE (Dev1.type NOT LIKE '%cable%') AND (id = Dev1.parent_device_id))T1_DeviceID,

(SELECT name
 FROM Symbol
 WHERE (id = CP1.symbol_id) AND (type NOT LIKE '%cable%'))T1_DeviceName,

(SELECT name
 FROM Location
 WHERE (id = Page.location_id))T1_Location,

(SELECT name
 FROM Installation
 WHERE (id = Page.installation_id))T1_Installation,

(SELECT name
 FROM ParentDevice
 WHERE (Dev2.type NOT LIKE '%cable%') AND (id = Dev2.parent_device_id))T2_DeviceID,

(SELECT name
 FROM Symbol
 WHERE ( id = CP2.symbol_id) AND (type NOT LIKE '%cable%'))T2_DeviceName,

(SELECT name
 FROM Location
 WHERE (id = PD2.location_id))T2_Location,

(SELECT name
 FROM Installation
 WHERE (id = Page.installation_id))T2_Installation,

(SELECT devicefamily
 FROM Device
 WHERE (type LIKE '%cable%') AND (id = SymCable.device_id))CablePartNumber,

(SELECT name
 FROM ParentDevice
 WHERE (id = DevCable.parent_device_id) AND (DevCable.type LIKE '%cable%'))CableTag

FROM dbo.LogicalLine
          INNER JOIN dbo.Page ON dbo.LogicalLine.page_id = dbo.Page.id
          INNER JOIN dbo.Branch ON dbo.LogicalLine.branch_id = dbo.Branch.id
          LEFT OUTER JOIN dbo.Symbol AS SymCable ON dbo.LogicalLine.cable_id = SymCable.id
          LEFT OUTER JOIN dbo.Device AS DevCable ON SymCable.device_id = DevCable.id
          LEFT OUTER JOIN dbo.ParentDevice AS ParentCable ON DevCable.parent_device_id = ParentCable.id
          INNER JOIN dbo.SymbolCP AS CP1 ON dbo.Branch.cp1_id = CP1.id
          INNER JOIN dbo.SymbolCP AS CP2 ON dbo.Branch.cp2_id = CP2.id
          INNER JOIN dbo.Symbol AS S1 ON CP1.symbol_id = S1.id
          INNER JOIN dbo.Symbol AS S2 ON CP2.symbol_id = S2.id
          INNER JOIN dbo.Device AS Dev1 ON S1.device_id = Dev1.id
          INNER JOIN dbo.Device AS Dev2 ON S2.device_id = Dev2.id
          INNER JOIN dbo.ParentDevice AS PD1 ON Dev1.parent_device_id = PD1.id
          INNER JOIN dbo.ParentDevice AS PD2 ON Dev2.parent_device_id = PD2.id
          INNER JOIN dbo.Location AS L1 ON PD1.location_id = L1.id 
          INNER JOIN dbo.Location AS L2 ON PD2.location_id = L2.id 
          INNER JOIN dbo.Installation AS I1 ON L1.installation_id = I1.id
          INNER JOIN dbo.Installation AS I2 ON L2.installation_id = I2.id

WHERE 
         (PD1.project_id = @Projectid)  AND (dbo.LogicalLine.drawingmode LIKE '%Single Line%');

Sorry I am unsure, I am currently using bentley promis.e software which requires SQL statements to create a custom report — Mike
– Mike, Commented Jan 17, 2017 at 23:09

Charles Bretana · Accepted Answer · 2017-01-17 23:45:11Z

1

Select Id, BranchId from table t
Where not exists
   (Select * from table
    where id != t.Id 
       and BranchId = t.BranchId)

or

 Select Id, BranchId
 From table
 Group By BranchId
 Having count(*) == 1

EDIT: to modify as requested, simply add to your complete SQL query a Where clause:

 Select l.Id BranchId, [plus whatever else you have in your select clause]
 FROM LogicalLine l
      join Page p ON p.id = l.page_Id 
      join Branch b ON b.Id = l.branch_id 
 Group By l.branch_id, [Plus whatever else you have in Select clause]
 Having count(*) == 1

or

Select l.Id BranchId, [plus whatever else you have in your select clause]
FROM LogicalLine l
      join Page p on p.id = l.page_Id 
      join Branch b on b.Id = l.branch_id 
Where not exists
   (Select * from LogicalLine 
    where id != l.Id 
       and branch_id = l.branch_id)

edited Jan 17, 2017 at 23:45

answered Jan 17, 2017 at 22:57

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mike Over a year ago

Thanks for the reply, if possible can you show how I can apply this to the above edit?

Charles Bretana Over a year ago

I edited answer to guess. If you show entire sql query (or at least the Select clause), I could be more helpful.,

Mike Over a year ago

What I want to do is have a large relational table which has been filtered so I can Select what I want in the select query from this table. Hence I wish to add it in the FROM dbo.LogicalLine

Charles Bretana Over a year ago

Filters (Predicates) go in the Where Clause. Just modify your where clause, add another predicate

Collectives™ on Stack Overflow

SQL Filtering rows with no duplicate value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related