CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
MisterO
MisterO

Reputation: 21

select rows where certain field appears only in multiple rows

I'm wanting to pull out information from a number of tables, but only want to show the rows when a person has multiple entries, i.e. more than one row of information

This is the code as it is. I've been toying with using HAVING count (person) > 1 using various methods but can't seem to get it right.

All help much appreciated.

SELECT DISTINCT
          table1.person
        , table1.id
        , table1.type
        , table1.active
        , table2.stage
        , table3.start
        , table3.end

FROM
          people table1
        , storyType table2
        , storyContract table3

WHERE
        table1.type = table2.type_id
    AND table2.stage = 1
    AND table1.id = table3.contract_id
    AND table3.start > '2015-01-01 00:00:00'

Current results are

person          id      start               end                 type    active  stage

Keith Richards  202971  24/11/2015 00:00    07/03/2016 00:00    8       1       Amber
Keith Richards  218325  07/03/2016 00:00    07/04/2016 00:00    10      1       Red
Steve Perryman  217788  02/03/2016 00:00    04/07/2016 00:00    8       1       Amber
Cyril Knowles   202438  20/11/2015 00:00    24/06/2016 00:00    10      1       Red
Pat Jennings    215324  11/02/2016 00:00    29/08/2016 00:00    8       1       Amber
Alan Gilzean    200575  06/11/2015 00:00    08/11/2015 00:00    8       1       Amber
Bill Wyman      203575  27/11/2015 00:00    14/01/2016 00:00    8       1       Amber
Bill Wyman      209740  14/01/2016 00:00    10/03/2016 00:00    9       1       Green
Bill Wyman      219330  11/03/2016 00:00    01/09/2016 00:00    10      1       Red
Mike England    209288  12/01/2016 12:54    01/02/2016 12:54    8       1       Amber
Charlie Watts   198363  14/10/2015 12:40    05/11/2015 00:00    8       1       Amber
Charlie Watts   200281  05/11/2015 00:00    13/06/2016 00:00    10      1       Red
Brian Jones     208265  06/01/2016 14:38    04/02/2016 00:00    8       1       Amber
Brian Jones     214052  04/02/2016 00:00    17/03/2016 00:00    9       1       Green
Brian Jones     220425  17/03/2016 00:00    04/07/2016 00:00    10      1       Red
Martin Chivers  209195  12/01/2016 00:00    04/07/2016 00:00    8       1       Amber
Alan Mullery    212919  29/01/2016 00:00    04/07/2016 00:00    8       1       Amber
Mick Jagger     199134  20/10/2015 00:00    17/12/2015 00:00    8       1       Amber
Mick Jagger     212690  28/01/2016 00:00    24/06/2016 00:00    8       1       Amber
Martin Peters   195833  30/09/2015 00:00    04/07/2016 00:00    8       1       Amber

Results wanted

person          id      start               end                 type    active  stage

Keith Richards  202971  24/11/2015 00:00    07/03/2016 00:00    8       1       Amber
Keith Richards  218325  07/03/2016 00:00    07/04/2016 00:00    10      1       Red
Bill Wyman      203575  27/11/2015 00:00    14/01/2016 00:00    8       1       Amber
Bill Wyman      209740  14/01/2016 00:00    10/03/2016 00:00    9       1       Green
Bill Wyman      219330  11/03/2016 00:00    01/09/2016 00:00    10      1       Red
Charlie Watts   198363  14/10/2015 12:40    05/11/2015 00:00    8       1       Amber
Charlie Watts   200281  05/11/2015 00:00    13/06/2016 00:00    10      1       Red
Brian Jones     208265  06/01/2016 14:38    04/02/2016 00:00    8       1       Amber
Brian Jones     214052  04/02/2016 00:00    17/03/2016 00:00    9       1       Green
Brian Jones     220425  17/03/2016 00:00    04/07/2016 00:00    10      1       Red
Mick Jagger     199134  20/10/2015 00:00    17/12/2015 00:00    8       1       Amber
Mick Jagger     212690  28/01/2016 00:00    24/06/2016 00:00    8       1       Amber

Upvotes: 1

Views: 64

Answers (4)

sallushan
sallushan

Reputation: 1147

Find the table which has person lines repeated and filter them out, probably your storyContract table has person repeated,

SELECT /* DISTINCT <- no need for distinct probably */
          table1.person
        , table1.id
        , table1.type
        , table1.active
        , table2.stage
        , table3.start
        , table3.end

FROM
          people table1
        , storyType table2
        , storyContract table3

WHERE
        table1.type = table2.type_id
    AND table2.stage = 1
    AND table1.id = table3.contract_id
    AND table3.start > '2015-01-01 00:00:00'
    -- ----- additional filter ----------------------
    AND Exists(
          SELECT  1
          FROM    storyContract as c
          WHERE   c.contract_id = table1.id
          HAVING  COUNT(1) > 1
    )
    -- ----------------------------------------------

Upvotes: 0

S&#248;ren Kongstad
S&#248;ren Kongstad

Reputation: 1440

You can adapt TheGameiswar's solution: Use the analytical function count in stead of row_number

;With cte
as
(
--this can be your join logic.Replace everything except count(*) from join
select 
*,count(*) over (partition by person) as PersonCount
from
table
)
select * from cte where PersonCount>1

Upvotes: 1

djangojazz
djangojazz

Reputation: 13232

Use a having statement to get results with a count criteria and then join to that.

Example:

DECLARE @Orders TABLE (Person VARCHAR(32), Ord VARCHAR(128));

INSERT INTO @Orders (Person, Ord) VALUES ('Brett', 'Shirt'), ('Brett', 'Pants'), ('Mary', 'Dress')

--Everything as is
Select *
From @Orders


-- getting ordersOver2
; With ordersOver2 as 
    (
    Select 
        Person
    ,   COUNT(Ord) AS cnt
    FROM @Orders
    GROUP BY Person
    HAVING COUNT(Ord) > 1
    )
-- relating this to main set
Select a.*
From @Orders a
    JOIN ordersOver2 b ON b.Person = a.Person

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28890

;With cte
as
(
--this can be your join logic.Replace everything except rownum from join
select 
*,row_number() over (partition by person order by (select null)) as rownum
from
table
)
select * from cte where rownum>1

Upvotes: 0

Related Questions