SQL - detect exact matches across multiple records

Question

Table Parent

Column1
S1
S2
S3

Table child

Column1     Column2
S1          P1
S1          P2
S2          P1
S2          P2
S3          P1

Where parent.column1 = child.column1

Given the tables above, I need to identify the parents whose children have the same entries in column2 as the parent S1.

For example, S1 and S2 have P1 and P2, so this will meet the condition. S3, however, does not have P2 and therefore should be excluded.

New to SQL, so I have problems. Tried this using the not instruction, but since S3 has P1, it is not ruled out.

Answer 1

You need to join. It depends on the dialect of SQL. Sort of:

select child.column1, child.column2 from (
  select column2 as parentsColumn2Value from child where column1='S1'
) as parentsColumn2Table
left join child on parentsColumn2Table.column2=child.column2

Answer 2

:) . , ( ).

, "" , Access ( SQL Server), , . (, , ...)

SELECT p1.column1, p2.column1
FROM parent p1 JOIN parent p2 ON p1.column1 < p2.column1
WHERE NOT EXISTS (SELECT 1
       FROM (SELECT c1.column1, c1.column2 FROM child c1 WHERE c1.column1 = p1.column1) c1f
            FULL OUTER JOIN
            (SELECT c2.column1, c2.column2 FROM child c2 WHERE c2.column1 = p2.column1) c2f
            ON c1f.column2 = c2f.column2
       WHERE c1f.column1 IS NULL OR c2f.column1 IS NULL
      );

, , , , , :) ...

"" () 1 (p1.column1 p2.column1). "child" ( c1f c2f: c1f "child 1 filter" ) FULL OUTER JOIN. , . c1f c2f ( 1) , . , 1. , 1, , p1 column1 p2 column1 .

, , , p1.column1 "S1", p2.column2 - "S3", ( ) :

 c1f__column1 | c1f__column2 | c2f__column1 | c2f__column2
--------------+--------------+--------------+--------------
 S1           | P1           | S3           | P1
 S1           | P2           |              |

, . , .

, , (column1, column2) , , , . ! ( , ... "" c1f c2f)

NB , (.. , S1), " p1.column1 =" S1 " . " parent p1 JOIN parent p2 ON p1.column1 < p2.column1 " " p1, p2" ... , , , ...

Answer 3

Access, SQL-, User Defined Function (UDF).

SELECT p.Column1, 
       ConcatList("SELECT Column2 FROM c WHERE Column1='S1'","|") AS S1, 
       ConcatList("SELECT Column2 FROM c WHERE Column1='" & [p].[Column1] & "'","|") AS Child, 
       Format([S1]=[Child],"Yes/No") AS [Match]
FROM p;

UDF

   Function ConcatList(strSQL As String, strDelim, ParamArray NameList() As Variant)
   ''Reference: Microsoft DAO x.x Object Library
   Dim db As Database
   Dim rs As DAO.Recordset
   Dim strList As String

   Set db = CurrentDb

   If strSQL <> "" Then
       Set rs = db.OpenRecordset(strSQL)

       Do While Not rs.EOF
           strList = strList & strDelim & rs.Fields(0)
           rs.MoveNext
       Loop

       strList = Mid(strList, Len(strDelim) + 1)
   Else

       strList = Join(NameList, strDelim)
   End If

   ConcatList = strList

   End Function

Unfortunately, I don’t think Jet supports Full Outer Join, so a solution using only SQL is likely to be a bit tedious and will require additional information, such as whether S1 has a fixed number of entries in column2.

Answer 4

ACE / Jet may not support FULL OUTER JOIN directly, but the workaround is quite simple, i.e. just UNION ALL LEFT JOIN, INNER JOIN and RIGHT JOIN respectively from the tables.

Answer 5

By the way, I was looking for the same solution and understood it myself.

select * from TableParent where id in 
  (select temp_parent.id from
    (select TableParent.*, count(exact_match.match_count) as exact_count 
     from TableParent
     inner join 
       (select Column1, count(*) as match_count from TableChild
        group by Column1
        having match_count = 2
       ) as exact_match on exact_match.Column1 = TableParent.Column1
     inner join 
       TableChild on TableChild.event_id = exact_match.Column1 where TableChild.Column2 in (P1,P2)
   group by TableParent.Column1
   having exact_count = 2) as temp_parent)