CODEWITHSUNDEEP
sqlsql-server
user3799325
user3799325

Reputation: 610

Find Material that are assigned to more than one category

What would be the query to find List of material that are assigned to more than one maincode.

Here is the sample data

CREATE TABLE #MaterialMainCodetemp (
    MaterialCode VARCHAR(100),
    Maincode varchar(100)
);
Insert into #MaterialMainCodetemp Values('Material1','Main1')
Insert into #MaterialMainCodetemp Values('Material5','Main1')
Insert into #MaterialMainCodetemp Values('Material2','Main1')
Insert into #MaterialMainCodetemp Values('Material3','Main1')
Insert into #MaterialMainCodetemp Values('Material4','Main1')
Insert into #MaterialMainCodetemp Values('Material1','Main2')
Insert into #MaterialMainCodetemp Values('Material6','Main2')
Insert into #MaterialMainCodetemp Values('Material7','Main2')
Insert into #MaterialMainCodetemp Values('Material8','Main3')
Insert into #MaterialMainCodetemp Values('Material4','Main4')


Select M.MaterialCode,Count(*) from #MaterialMainCodetemp M
GROUP By M.MaterialCode
Having Count(*) > 1

Above query Returns this, but i should only get back Material1, since only that material is assign two different MainCode

MaterialCode    (No column name)
Material4          2
Material1          2

What Query would look like that will return Material1 only ?

Upvotes: 0

Views: 41

Answers (1)

pwilcox
pwilcox

Reputation: 5753

I think your sample data is off. Material4 is in fact assigned two different main codes. Change your last insert statement to:

Insert into #MaterialMainCodetemp Values('Material4','Main1')

or drop it entirely.

However, your query would still give you that result even if you changed it. So use count(distinct maincode):

Select M.MaterialCode,Count(distinct maincode) from #MaterialMainCodetemp M
GROUP By M.MaterialCode
Having Count(distinct maincode) > 1

Upvotes: 1

Related Questions