| Author |
 Topic  |
|
|
sebastian11c
Posting Yak Master
125 Posts |
 Posted - 04/19/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/19/2012 : 15:19:00
|
If you just want the item numbers use thisselect iditem from Process
where state in ('begin','half','complete')
group by iditem having count(distinct state) = 3If 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); |
Edited by - sunitabeck on 04/19/2012 15:19:57 |
 |
|
|
sebastian11c
Posting Yak Master
125 Posts |
Posted - 04/19/2012 : 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
India
47069 Posts |
Posted - 04/19/2012 : 16:35:44
|
sounds like addition of a check constraint to me.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sebastian11c
Posting Yak Master
125 Posts |
Posted - 04/19/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/20/2012 : 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
Sweden
29138 Posts |
|
|
sebastian11c
Posting Yak Master
125 Posts |
Posted - 04/20/2012 : 09:17:35
|
thanks sunitabeck and SwePeso
thanks for alwayss helping the beginners ,
ill try this solutions
many many thanks |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
|
| |
Topic |
|
specific select
