Untitled

SQL Query question - How to select groups of rows using commonalities
select streetaddress, count(*) as occupantcount
       from census
       where firstname in ("Julia", "Hannah", "Helen")
       group by streetaddress
       order by occupantcount desc

SELECT Address, FamilyName, Forename
FROM   YourTable
WHERE  Address IN (SELECT Address
                   FROM   YourTable
                   WHERE  Forename IN ( 'Julia', 'Hannah', 'Helen' )
                   GROUP  BY Address
                   HAVING COUNT(DISTINCT Forename) = 3)

WITH Names(name)
     AS (SELECT 'Julia'
         UNION ALL
         SELECT 'Hannah'
         UNION ALL
         SELECT 'Helen')
SELECT Address,
       FamilyName,
       Forename
FROM   YourTable y1
WHERE  NOT EXISTS (SELECT *
                   FROM   Names n
                   WHERE  NOT EXISTS(SELECT *
                                     FROM   YourTable y2
                                     WHERE  y1.Address = y2.Address
                                            AND y2.Forename = n.Name))

SELECT A.*
FROM YourTable A
JOIN (  SELECT Address, COUNT(*) Quant
        FROM YourTable
        WHERE Forename IN ('Julia','Hannah','Helen')
        GROUP BY Address
        HAVING COUNT(DISTINCT Forename) > 2) B
ON A.Address = B.Address

SELECT a.Address as CommonAddress
FROM (SELECT Address FROM Names WHERE Forename = 'Julia') a
INNER JOIN (SELECT Address FROM Names WHERE Forename = 'Richard') b ON a.Address=b.Address
INNER JOIN (SELECT Address FROM Names WHERE Forename = 'Helen') c on b.Address=c.Address

declare @people table (
    address varchar(255),
    familyName varchar(255),
    forename varchar(255)
)

insert into @people
values ('Residents of a house 2 in Janemount Lower (Cork No. 4 Urban (part of), Cork)', 'Gamble', 'Julia')
insert into @people
values ('Residents of a house 2 in Janemount Lower (Cork No. 4 Urban (part of), Cork)', 'Gamble', 'Richard')
insert into @people
values ('Residents of a house 2 in Janemount Lower (Cork No. 4 Urban (part of), Cork)', 'Gamble', 'Hannah')
insert into @people
values ('Residents of a house 2 in Janemount Lower (Cork No. 4 Urban (part of), Cork)', 'Gamble', 'Helen')
insert into @people
values ('Residents of a house 2 somewhere else (Cork No. 4 Urban (part of), Cork)', 'Cooper', 'Helen')

select people.*
from @people as people
where people.address in (
    select address
    from @people
    where forename in ('Julia', 'Hannah', 'Helen')
    group by address
    having count(forename) >= 3 -- This must be equal to the number of names searched for
)

SELECT t.address
     , t.familyname
     , t.forename
  FROM yourTable t
  WHERE t.address IN
    -- search subquery
    ( SELECT s1.address
        FROM yourTable s1
          JOIN yourTable s2
            ON s2.address = s1.address
          JOIN yourTable s3
            ON s3.address = s1.address
        WHERE s1.forename = "Julia"
          AND s2.forename = "Hannah"
          AND s3.forename = "Helen"
    )
  ORDER BY t.address
         , t.familyname
         , t.forename
;

SELECT t.address
     , t.familyname
     , t.forename
  FROM yourTable t
  WHERE EXISTS
    -- search subquery
    ( SELECT *
        FROM yourTable s1
          JOIN yourTable s2
            ON s2.address = s1.address
          JOIN yourTable s3
            ON s3.address = s1.address
        WHERE s1.forename = "Julia"
          AND s2.forename = "Hannah"
          AND s3.forename = "Helen"
          AND s1.address = t.address
    )
  ORDER BY t.address
         , t.familyname
         , t.forename
;

SELECT t.address
     , t.familyname
     , t.forename
  FROM yourTable t
  WHERE  -- search subqueries
    EXISTS
      ( SELECT *
          FROM yourTable s1
            WHERE s1.forename= "Julia"
              AND s1.address = t.address
      )
    AND EXISTS
      ( SELECT *
          FROM yourTable s2
            WHERE s2.forename = "Hannah"
              AND s2.address = t.address
      )
    AND EXISTS
      ( SELECT *
          FROM yourTable s3
            WHERE s3.forename = "Helen"
              AND s3.address = t.address
      )
  ORDER BY t.address
         , t.familyname
         , t.forename
;

DECLARE @namecount int;
DECLARE @forenames TABLE (name varchar(50));
INSERT INTO @forenames
VALUES ('...'),
       ('...'),
       ('...');
SELECT @namecount = COUNT(*) FROM @forenames;

/* list all people by addresses that are shared by people
   whose forenames are included in @forenames */
SELECT cd.*
FROM CensusData cd
  INNER JOIN (
    SELECT d.Address
    FROM CensusData d
      INNER JOIN @forenames f ON d.Forename = f.Name
    GROUP BY d.Address
    HAVING COUNT(DISTINCT d.Forename) >= @namecount
  ) filter ON cd.Address = filter.Address

/* same for family names */
SELECT cd.*
FROM CensusData cd
  INNER JOIN (
    SELECT d.[Family Name]
    FROM CensusData d
      INNER JOIN @forenames f ON d.Forename = f.Name
    GROUP BY d.Address
    HAVING COUNT(DISTINCT d.Forename) >= @namecount
  ) filter ON cd.[Family Name]= filter.[Family Name]

/* and so on fro other criteria */

select *
from dataset
where address = (
    select address
    from dataset
    where famly_name = 'Hannah' and forename = 'Gamble'
)

Select
    T1.*
From
    TableName T1
Inner Join
    TableName T2
On
    T1.Address = T2.Address
Where
    T1.ForeName = 'Julia'