Group By statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Group By statement

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2007-05-16 : 16:19:19

Below is a challenge I have tried to solve a couple of different ways, but it seems there must be an easier way.

Here is my table structure/example data:

productID, productAttribute
1,100
1,105
1,106
2,106
2,100
3,109
3,110
3,111

I want to:
select productID that have (productAttribute = 100 and productAttribute = 106)

...so I need to group by productID and find only the products that have both attributes.

Thanks in Advance!
doug

Create a Family Website! Share Photos, News, Polls, Calendar, Address Book and more! Visit www.familydetails.com for a free 30-day trial.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-16 : 16:44:16

select t1.productid
from (select productid from table1 where product attribute = 100) as t1
inner join (select productid from table1 where product attribute = 106) as t2 on t2.productid = t1.prodctid

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-16 : 16:44:58

select productID
from yourtable
where productAttribute in (100,106)
group by productID
having Count(*) = 2

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-16 : 16:48:38

[code]select productid
from (
select productid,
max(case
when productattribute = 100 then 1
else 0
end) as pa100,
max(case
when productattribute = 106 then 1
else 0
end) as pa100
from table1
group by productid
) as d
where pa100 = 1
and pa106 = 1[/code]

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-16 : 18:01:48

Peso -- why over-complicate it? Simple = good, right?

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-17 : 02:30:40

I agrees, until there is a duplicate of {1,106} in which your suggestion fails.
OP didn't state if there is a possibility of this. If there is not, your suggestion is the best.
If there is, my will work.

Or a rewrite of yours will handle all cases.

select productID
from yourtable
where productAttribute in (100,106)
group by productID
having Count(distinct productAttribute) = 2

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-17 : 08:13:01

You are correct, it all depends on the table structure. we have no idea, because, unfortunately like usual, it was not indicated.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources