Geek asks and answers

SQL Query question - How to select row groups using common features

I work with census data, and I want to provide the ability to search for records by providing a list of names. The idea is that if you know the name or 2 or 3 family members, you can exclude all addresses that do not have these names. Consider this example dataset (Address, Last name, First name):

"Residents of house 2 in the lower part of the Lower (Cork No. 4 City (part), Cork)", "Gamble", "Julia"
"Residents of the house 2 in the Lower Lower (Cork No. 4 City (part), Cork)", "Gamble "," Richard "
" Residents of House 2 in Lower Nizhny (Cork No. 4 City (part), Cork) "," Gamble "," Hannah "
" Residents of House 2 in Lower Nizhny (Cork No. 4 City (part), Cork) "," Gamble "," Helen

A search for Julia, Hannah and Helen should be able to return all 4 lines, since they have a common address. It sounds simple enough, but I have problems with that. Cursors fail due to the size of the data set. Any ideas?

(Needless to say, I simplified this somewhat, as I ignore the part of the family name for the search)

+3
source share
9 answers

You can try the following:

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
+1
source

This is .

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)

or

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))
+1
source

This request:

       select streetaddress, count(*) as occupantcount
       from census
       where firstname in ("Julia", "Hannah", "Helen")
       group by streetaddress
       order by occupantcount desc

, IN(), . ( ) . .

+1
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 
;
0

, :

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
0

, , , , .

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
)

One problem with this solution is that if there are three people living at the same address, the request will return those people, even if Julia and Helen do not live there.

0
source 
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 */

You can also combine criteria if necessary.

0
source 
select *
from dataset
where address = (
    select address
    from dataset
    where famly_name = 'Hannah' and forename = 'Gamble'
)
-1
source

You can start with something like this, assuming the address must be exactly consistent. You need to configure it, as you are likely to see some duplicates.

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

More articles:


All Articles