Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
HI
 
I am trying to do a count of productserials from a table.
 
I currently have a view called listStages which is a union statement of the following tables(receive,soak,test,repair,software, dispatch)
 
each table in the brackets has a passfail.pass = 1 fail = 0
 
ListStages shows each product and if it was passed or failed at a stage.
 
I would like to know how would i be able to select the products which only have a pass(1) on each table and if it has a fail then it should not show in my records which are returned by the query
Posted 7-Mar-13 23:20pm
isi19463
Edited 7-Mar-13 23:23pm
v2
Comments
Davidduraisamy at 8-Mar-13 4:24am
   
Restrict the condition in where clause as pass=1
isi19 at 8-Mar-13 4:39am
   
See the only thing is that the product has to be passed to be dispatched , the process will follow Rceive pass --> soak pass --> test pass --> dispatch
 
if there is a fail
 
Rceive pass--> soak fail--> repair Pass--> soak pass--> test pass--> dispatch
 
now i need to select only the products that follow the 1st process.
if i select * from ListStage where passfail = 1 then it will return the value of the product which had a repair. and i want it to show only the products which do not have a repair
Davidduraisamy at 8-Mar-13 4:46am
   
hey if you are applying where condition in all table den how fail condition will come
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
Simplest way is to use pass = 1 in where condition for all tables
or use a common table expression , write your union query inside it and use where condition at output
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

I'm not sure wjat you want to do, but based on your comment:
Rceive pass--> soak fail--> repair Pass--> soak pass--> test pass--> dispatch
now i need to select only the products that follow the 1st process. if i select * from ListStage where passfail = 1 then it will return the value of the product which had a repair. and i want it to show only the products which do not have a repair
i recommend you to do something like this:
SELECT LS.*
FROM listStages AS LS LEFT JOIN Rceive AS RC ON LC.ProductID = RC.ProductID
WHERE RC.Pass=1
or
SELECT *
FROM listStages 
WHERE ProductID IN (
    SELECT ProductID
    FROM Rceive
    WHERE Pass=1)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

As per my understanding ... you want this ....
 
 
Select stage,product,SUM(CAST(passfail  AS numeric))  from listStages  
GROUP BY stage,product
Having SUM(CAST(passfail AS numeric)) = COUNT(1)  
 
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 8,284
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,614
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 8 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100