SQL Elaborate Joins Query

Question

I'm trying to solve the below problem.

I feel like it is possible, but I can't seem to get it.

Here's the scenario:

Table 1 (Assets)
1 Asset-A
2 Asset-B
3 Asset-C
4 Asset-D

Table 2 (Attributes)
1 Asset-A Red
2 Asset-A Hard
3 Asset-B Red
4 Asset-B Hard
5 Asset-B Heavy
6 Asset-C Blue
7 Asset-C Hard

If I am looking for something having the same attributes as Asset-A, then it should identify Asset-B since Asset-B has all the same attributes as Asset-A (it should discard heavy, since Asset-A didn't specify anything different or the similar). Also, if I wanted the attributes for only Asset-A AND Asset-B that were common, how would I get that?

Seems simple, but I can't nail it...

The actual table I am using, is almost precisely Table2, simply an association of an AssetId, and an AttributeId so: PK: Id
int: AssetId
int: AttributeId

I only included the idea of the asset table to simplify the question.

Answer 1

SELECT  ato.id, ato.value
FROM    (
        SELECT  id
        FROM    assets a
        WHERE   NOT EXISTS
                (
                SELECT  NULL
                FROM    attributes ata
                LEFT  JOIN
                        attributes ato
                ON      ato.id = ata.id
                        AND ato.value = ata.value
                WHERE   ata.id = 1
                        AND ato.id IS NULL
                )
        ) ao
JOIN    attributes ato
ON      ato.id = ao.id
JOIN    attributes ata
ON      ata.id = 1
        AND ata.value = ato.value

, or in SQL Server 2005 (with sample data to check):

WITH    assets AS 
        (
        SELECT 1 AS id, 'A' AS name
        UNION ALL
        SELECT 2 AS id, 'B' AS name
        UNION ALL
        SELECT 3 AS id, 'C' AS name
        UNION ALL
        SELECT 4 AS id, 'D' AS name
        ),
        attributes AS
        (
        SELECT 1 AS id, 'Red' AS value
        UNION ALL
        SELECT 1 AS id, 'Hard' AS value
        UNION ALL
        SELECT 2 AS id, 'Red' AS value
        UNION ALL
        SELECT 2 AS id, 'Hard' AS value
        UNION ALL
        SELECT 2 AS id, 'Heavy' AS value
        UNION ALL
        SELECT 3 AS id, 'Blue' AS value
        UNION ALL
        SELECT 3 AS id, 'Hard' AS value
        )
SELECT  ato.id, ato.value
FROM    (
        SELECT  id
        FROM    assets a
        WHERE   a.id <> 1
                AND NOT EXISTS
                (
                SELECT  ata.value
                FROM    attributes ata
                WHERE   ata.id = 1
                EXCEPT
                SELECT  ato.value
                FROM    attributes ato
                WHERE   ato.id = a.id
                )
        ) ao
JOIN    attributes ato
ON      ato.id = ao.id
JOIN    attributes ata
ON      ata.id = 1
        AND ata.value = ato.value

Answer 2

I don't completely understand the first part of your question, identifying assets based on their attributes.

Making some assumptions about column names, the following query would yield the common attributes between Asset-A and Asset-B:

SELECT [Table 2].Name
FROM [Table 2]
JOIN [Table 1] a ON a.ID = [Table 2].AssetID AND a.Name = 'Asset-A'
JOIN [Table 1] b ON b.ID = [Table 2].AssetID AND b.Name = 'Asset-B'
GROUP BY [Table 2].Name

Answer 3

 Select * From Assets A
    Where Exists 
      (Select * From Assets
       Where AssetId <> A.AssetID
          And (Select Count(*)
               From Attributes At1 Join Attributes At2
                  On At1.AssetId <> At2.AssetId
                      And At1.attribute <> At2.Attribute
               Where At1.AssetId = A.AssetId Asset) = 0 )
    And AssetId = 'Asset-A'

Answer 4

select at2.asset, count(*)
from       attribute at1
inner join attribute at2 on at1.value = at2.value
where at1.asset =  "Asset-A"
and   at2.asset != "Asset-A"
group by at2.asset
having count(*) = (select count(*) from attribute where asset = "Asset-A");

Answer 5

Find all assets who have every attribute that "A" has (but also may have additional attributes):

SELECT Other.ID
FROM Assets Other
WHERE
  Other.AssetID <> 'Asset-A'   -- do not return Asset A as a match to itself
  AND NOT EXISTS (SELECT NULL FROM Attributes AttA WHERE
    AttA.AssetID='Asset-A' 
    AND NOT EXISTS (SELECT NULL FROM Attributes AttOther WHERE
      AttOther.AssetID=Other.ID AND AttOther.AttributeID = AttA.AttributeID
      )
    )

I.e., "find any asset where there is no attribute of A that is not also an attribute of this asset".

Find all assets who have exactly the same attributes as "A":

SELECT Other.ID
FROM Assets Other
WHERE
  Other.AssetID <> 'Asset-A'   -- do not return Asset A as a match to itself
  AND NOT EXISTS (SELECT NULL FROM Attributes AttA WHERE
    AttA.AssetID='Asset-A' 
    AND NOT EXISTS (SELECT NULL FROM Attributes AttOther WHERE
      AttOther.AssetID=Other.ID 
      AND AttOther.AttributeID = AttA.AttributeID
      )
    ) 
  AND NOT EXISTS (SELECT NULL FROM Attributes AttaOther WHERE
    AttaOther.AssetID=Other.ID 
    AND NOT EXISTS (SELECT NULL FROM Attributes AttaA WHERE
      AttaA.AssetID='Asset-A' 
      AND AttaA.AttributeID = AttaOther.AttributeID
      )
   )

I.e., "find any asset where there is no attribute of A that is not also an attribute of this asset, and where there is no attribute of this asset that is not also an attribute of A."

Answer 6

This solution works as prescribed, thanks for the input.

WITH Atts AS 
(
    SELECT
    DISTINCT
     at1.[Attribute]
    FROM
     Attribute at1
    WHERE
     at1.[Asset] = 'Asset-A'
)

SELECT 
    DISTINCT
    Asset,
    (
     SELECT 
      COUNT(ta2.[Attribute]) 
     FROM 
      Attribute ta2 
     INNER JOIN
      Atts b 
      ON
       b.[Attribute] = ta2.[attribute]
     WHERE 
      ta2.[Asset] = ta.Asset
    ) 
    AS [Count]
FROM 
    Atts a
INNER JOIN
    Attribute ta
    ON
    a.[Attribute] = ta.[Attribute]

Answer 7

Find all assets that have all the same attributes as asset-a:

select att2.Asset from attribute att1
inner join attribute att2 on att2.Attribute = att1.Attribute and att1.Asset <> att2.Asset
where att1.Asset = 'Asset-A'
group by att2.Asset, att1.Asset
having COUNT(*) = (select COUNT(*) from attribute where Asset=att1.Asset)

Answer 8

I thought maybe I can do this with LINQ and then work my way backwards with:

var result = from productsNotA in DevProducts
       where  productsNotA.Product != "A" && 
      (
       from productsA in DevProducts
       where productsA.Product == "A"
       select productsA.Attribute
      ).Except
      (
       from productOther in DevProducts
       where productOther.Product == productsNotA.Product
       select productOther.Attribute
      ).Single() == null
      select new {productsNotA.Product};

result.Distinct()

I thought that translating this back to SQL with LinqPad would result into a pretty SQL query. However it didn't :). DevProducts is my testtable with a column Product and Attribute. I thought I'd post the LINQ query anyways, might be useful to people who are playing around with LINQ.

If you can optimize the LINQ query above, please let me know (it might result in better SQL ;))

Answer 9

I'm using following DDL

CREATE TABLE Attributes (
    Asset      VARCHAR(100)
    , Name     VARCHAR(100)
    , UNIQUE(Asset, Name)
    )

Second question is easy

SELECT   Name
FROM     Attributes
WHERE    Name IN (SELECT Name FROM Attributes WHERE Asset = 'A')
         AND Asset = 'B'

First question is not more difficult

SELECT   Asset
FROM     Attributes
WHERE    Name IN (SELECT Name FROM Attributes WHERE Asset = 'A')
GROUP BY Asset
HAVING   COUNT(*) = (SELECT COUNT(*) FROM FROM Attributes WHERE Asset = 'A')

Edit:

I left AND Asset != 'A' out of the WHERE clause of the second snippet for brevity