SQLTeam.com | Weblogs | Forums

Selection problem


#1

See picture with problem

Hi!

I have a table with items which consist of some parts.
There are more possible versions for 1 items (different parts), but only 1 at the time is the main version: "main version = 1". I want now a list of all the items and their parts but only for the main versions.
It looks easy but OMG... I just can't find it..
who can help?

Thanks a lot!

Bart


#2

select t.code, t.version, t.codepart, t.mainversion -- why? from @tbl t where t.codepart is not null and exists ( select * from @tbl t1 where t1.code = t.code and t1.version = t.version and t1.mainversion = 1 )Does this work? I don't know. Since the data you supplied is in an image I can't copy and paste it. You'd probably get a much better response rate if you supplied the table creation and population scripts.


#4

Thanks a lot man, it works!! I should use the EXIST statement more often :wink:


#5

Woo-hoo!! Glad to have been of service.

Yes, I quite agree. It's often a very useful tool to have available.