Selecting unique items from linked

I’am using SQL Server 2012. I have store, where different sellers place their goods. Different sellers can place same goods. For example. Two sellers can place ‘iPhone’ as article. I need to find unique goods in the store.
I have two tables:

  1. ArticlePlacedBySeller {Id, Description, …}
  2. LinkBetweenArticles {Id, Article1, Article2 }

For example, if three sellers placed ‘iPhone’, and one ‘iPhone 2’ we will see:

  • MSSQL query in C#, Geting part of WHERE clause from a string
  • Avoiding branching in stored procedures that return search results?
  • Using apache camel, sql to create csv files
  • Getting Detailed SSRS Errors
  • Converting a decimal data type value to a formatted string
  • Get proper count of the rows from table
  • ArticlePlacedBySeller: {Id:1, Description:’iPhone super’}; {2, ‘iPhone best’}; {3, ‘iPhone new brand’}; {4, ‘iPhone 2’}

    LinkBetweenArticles: {Id:1, Article1:1, Article2:2}; {2, 2, 1}; {3, 2, 3}; {4, 3, 2}; {5, 3, 1}; {6, 1, 3};

    As you saw, only first three goods linked between each others. Fourth article is not linked. Now we need select just one (no matter what exactly) of this 3 goods from ArticlePlacedBySeller and 1 not linked by part of description (where Description like ‘%…%’). (result of this script must be:

    ArticlePlacedBySeller: Id:1, Id:4

    (two entities).

    Here is a script

    Can you suggest SQL query to do this?

    P.S. Please don’t suggest to change DB structure and add UniqueArticle table. I can’t do this and this is unuseful because very dynamically changing of goods in store.

    One Solution collect form web for “Selecting unique items from linked”

    Something like this will work for particular case, but might need additional testing on bigger sample data. It also assumes your links will always be bi-directional.

    ;WITH CTE_Links AS 
        SELECT Article1, MIN(Article2) Article2
        FROM dbo.LinkBetweenArticles
        GROUP BY Article1
        HAVING Article1 > MIN(Article2)
    SELECT a.* 
    FROM dbo.ArticlePlacedBySeller a
    LEFT JOIN CTE_Links l ON a.Id = l.Article1
    WHERE l.Article2 IS NULL

    SQLFiddle Demo

    Demo with few more articles

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.