Filter a table with relation table MS-SQL

Question

I need to filter a table with relation table in a MS-SQL database.

I get:

persId  persName1   catId

 4        Hans        0
 4        Hans        51
 4        Hans        73
 5        Torleif     0
 5        Torleif     5
 5        Torleif    73
 5        Torleif    76
 6        Peter       0
 6        Peter      51
 6        Peter      73
 7        Jonas       0
 7        Jonas      16
 7        Jonas      73

with:

SELECT   distinct  tblPerson.persId
, tblPerson.persName1
, relCatPers.catId
FROM         tblPerson LEFT OUTER JOIN
             relCatPers ON tblPerson.persId = relCatPers.persId

But i need. Remove those with catId 51. And only 1 occurrence of a name and Id:

persId  persName1

  5         Torleif
  7         Jonas

Table:

CREATE TABLE [dbo].[tblPerson](
    [persId] [int] IDENTITY(1,1) NOT NULL,
    [persName1] [varchar](255) NULL,
CONSTRAINT [PK_tblPerson] PRIMARY KEY CLUSTERED 
)


CREATE TABLE [dbo].[relCatPers](
    [rcpId] [int] IDENTITY(1,1) NOT NULL,
    [catId] [int] NOT NULL,
    [persId] [int] NOT NULL,
CONSTRAINT [PK_relCatPers] PRIMARY KEY CLUSTERED
)

No, 76 is higher. I want only the ones that do not have 51. And the another improvement would be that to not get the ones that have 51 and 16 etc.. — Per G
– Per G, Commented Mar 14, 2013 at 9:25
But if you want catId returned, but only one row per person, you have to tell SQL Server what catId should be returned. Do you always want catId = 73 as a result? — Josien
– Josien, Commented Mar 14, 2013 at 9:35

Indi · Accepted Answer · 2013-03-14 09:48:02Z

3

Try this:

Fiddle 1 demo here

select distinct p.persId, p.persName1 
from   tblPerson p left join 
         relCatPers c on p.persId = c.persId
where  p.persId not in
       (select persId from relCatPers where catId = 51)

Or you can ignore relCatPers table and do like below

Fiddle 2 demo here

select p.persId, p.persName1 
from   tblPerson p 
where  p.persId not in
       (select persId from relCatPers where catId = 51)

answered Mar 14, 2013 at 9:48

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josien Over a year ago

Nice, that second query without a join! +1

praveen · Accepted Answer · 2013-03-14 09:09:49Z

1

Try this :

With cte as 
(Select persId,persName1,
        row_number() over (partition by persId,persName1 order by persID) as rn
 from tblPerson 
)
Select cte.persID,cte.persName1
from relCatPers
left join cte
on tblPerson.persId = relCatPers.persId
where relCatPers.catid=51  and
rn=1

edited Mar 14, 2013 at 9:09

answered Mar 14, 2013 at 8:50

praveen

12.3k1 gold badge44 silver badges50 bronze badges

2 Comments

Per G Over a year ago

Last persId was wrong. So: With cte as (Select persId,persName1, row_number() over (partition by persId,persName1 order by persID) as rn from tblperson ) Select cte.persID,cte.persName1 from relCatPers left join cte on relCatPers.persId = relCatPers.persId where relCatPers.catid=51 and rn=1

Per G Over a year ago

But elegant solution. Do you think performance is better than the others? With select in select etc?

Josien · Accepted Answer · 2013-03-14 09:49:55Z

1

I've edited the query so it does not return a catId, as defined in your question:

SELECT distinct
       tblPerson.persId
       ,tblPerson.persName1
FROM   tblPerson
LEFT OUTER JOIN relCatPers ON tblPerson.persId = relCatPers.persId
WHERE  tblPerson.persId NOT IN 
       (SELECT persId FROM relCatPers WHERE catId = 51);

If you want to add additional catId's that should leave out persons, you can change the WHERE clause in the subquery to for example WHERE catId IN (16, 23, 51).

edited Mar 14, 2013 at 9:49

answered Mar 14, 2013 at 9:16

Josien

14k5 gold badges39 silver badges55 bronze badges

Collectives™ on Stack Overflow

Filter a table with relation table MS-SQL

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related