|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
October 22nd, 2005, 06:32 AM
|
|||
|
|||
|
SQl statements with condition
I need some help in the SQL select statements with the condition. The select should contain something like this where:-
One cust_id have many listing_id. Each listing carry status such as O and R. There are thousand's of cust_id with the status of 'O' and 'R'. If the cust_id have all 'O' status and no 'R' status, the another table will be updated.. If have 'O' and 'R' in that cust_id it will be skipped. What I need to do is to select cust_id that have all listing_id with the status of 'O' only and update in the another table. Do anyone have suggestions. Pls let me know... Thank You...
|
|
#3
October 23rd, 2005, 12:12 AM
|
|||
|
|||
|
Table Listing
Listing_Id Status Cust_Id 10001 O 00001 10002 O 00001 10003 R 00002 10004 O 00002 Table Customer Cust_Id ServiceType 00001 Active 00002 Active Only cust_id with the status of 'O' in all the listing will be picked up and update the customer table abd change the active to 'OB' status....Here cust_id '00001' will be changed to 'OB' Thanks....
|
|
#4
October 23rd, 2005, 12:50 PM
|
|||
|
|||
|
If you don't need to check servicetype, you can just use
Code:
SELECT cust_id ,SUM( CASE WHEN status != 'O' THEN 1 ELSE 0 END) AS nonO FROM listing GROUP BY cust_id HAVING nonO = 0 to get your 'O'-value cust_id's. If you need to check servicetype, you can use a join: Code:
SELECT l.cust_id ,SUM( CASE WHEN l.status != 'O' THEN 1 ELSE 0 END) AS nonO FROM listing AS l INNER JOIN customer AS c ON l.cust_id = c.cust_id WHERE c.servicetype = 'Active' GROUP BY l.cust_id HAVING nonO = 0
|
|
| Viewing: Dev Articles Community Forums > Databases > General SQL Development > SQl statements with condition |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
