specific select - 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 2008 Forums

Transact-SQL (2008)

specific select

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-04-19 : 15:10:19

hi there i have a table called "proccess"

and i need to do a select but only want to show the record that have a specific condition

my table

idproccess(identity) iditem state
1 142 begin
2 142 half
3 142 complete
4 190 begin
5 190 half
6 270 complete

i need a select that show me the iditem of the record that have all 3 states complete ( have begin, and half and complete )

my result in this case will be

only the iditem =142

i dont know hoew to do this select properly

many many thanks in advanced

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-19 : 15:19:00

If you just want the item numbers use this

select iditem from Process 
where state in ('begin','half','complete')
group by iditem having count(distinct state) = 3

If you want to get all three rows:

select * from Process
where state in ('begin','half','complete')
and iditem in (select iditem from Process group by iditem having count(distinct state) = 3);

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-04-19 : 16:02:42

thanks you so much for your answer it works great

now i have another question, how could i refuse an insert to my table if doesnt exist the state before,
let me explain,

if im trying to insert in my table the record ( iditem=700 and state ='complete' ) doesnt complete the transaction because the 'half' state doesnt exist and get an error message 0"you cannot insert the complete state , because you dont have the half state, first insert the half state :

idproccess(identity) iditem state
1 700 begin

thanks in advanced

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 16:35:44

sounds like addition of a check constraint to me.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-04-19 : 17:19:16

im sorry but my knowledge about sql is to low

visakh16, could you explain me how to add check constraint , that solve my problem

any example will be appreciate

thanks in advanced

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-20 : 08:58:41

If you are only inserting into or updating this table, a constraint would work. But, if you also delete the data from it and still want to maintain the condition that begin must exist if half exists etc., you would need a trigger.

If you don't delete, or if you don't care that when you delete the sequence need to be maintained, then you can use a constraint.

To do that, assuming the name of your table is XYZ, first create a function like shown below.

CREATE FUNCTION dbo.CheckStateSequencesOnXYZ
(
	@iditem  INT,
	@state   VARCHAR(32)
)
RETURNS INT

AS
BEGIN
	DECLARE @result INT = 0;
	IF (@state = 'begin') SET @result = 1;
	
	IF (@state = 'half' 
		AND EXISTS 
		(
			SELECT * FROM dbo.XYZ 
			WHERE STATE = 'begin' AND iditem = @iditem
		)
	) SET @result = 1;
	
	IF ( @state = 'complete' 
		AND EXISTS 
		(
			SELECT * FROM dbo.XYZ 
			WHERE STATE = 'half' AND iditem = @iditem
		)
	) SET @result = 1;
	
	RETURN @result;
END

It looks long, so there may be things you can do to make it shorter/simpler, but would need to implement same/similar logic based on your requirements.

Then add a constraint on your table like this:

ALTER TABLE dbo.XYZ ADD CONSTRAINT  
	XYZ_CheckStates CHECK (dbo.CheckStateSequencesOnXYZ(idItem,STATE) = 1);

There is a cost to doing this because the function will get called every time you want to insert/update the table.

Depending on your business logic/requirements, you may also want to add a unique constraint - for example like this:

ALTER TABLE dbo.XYZ ADD CONSTRAINT 
	XYZ_UniqueIdItemState UNIQUE CLUSTERED (iditem, state);

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-20 : 09:02:44

This is called Relational Division. Buy Deep Dives 2 and read more about it,
or read more here http://weblogs.sqlteam.com/peterl/archive/2010/06/30/Relational-division.aspx
or here http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx

N 56°04'39.26"
E 12°55'05.63"

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-04-20 : 09:17:35

thanks sunitabeck and SwePeso

thanks for alwayss helping the beginners ,

ill try this solutions

many many thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-20 : 09:41:56

quote:
Originally posted by SwePeso

This is called Relational Division. Buy Deep Dives 2 and read more about it,
or read more here http://weblogs.sqlteam.com/peterl/archive/2010/06/30/Relational-division.aspx
or here http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx

N 56°04'39.26"
E 12°55'05.63"

I ready through your article in Deep Dives. A little too mathematical for my taste, but still very nice, especially the examples!

For anyone looking for it, it is not in volume 1, it is in Volume 2 of the Deep Dives.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources