Complet Select stmt - not able to proceed

[baskee]

Hi Sql experts,
Please help me to resolve or give tips how to start on this.

the following are sample data in a table
I want an sql statement which displays only the records which
satisfies the following condn.

1.if type is 'BUS' and val = 1 and if type is FUN and date is null
2.if type is BUS and date is null and if type is FUN and val = 1
3.Do not fetch if type is BUS and val=1 and type is FUN and val=1

output for the table should be
num doc val date
--------------------
40 BUS 1 20080910
40 FUN 0 null
43 BUS 0 null
43 FUN 1 20080423

Sample data:

num doc val date
--------------------
40 BUS 1 20080910
40 FUN 0 null
41 BUS 0 20080910
42 BUS 0 20080713
42 FUN 1 null
43 BUS 0 null
43 FUN 1 20080423
44 BUS 1 null
44 FUN 1 null
45 BUS 1 20080722
45 FUN 1 null
46 BUS 1 20080923
46 FUN 0 20080722
47 FUN 1 null

I dont know how to perform two condition check in single query. please help.

[skhanal]

You can add multiple clauses on WHERE

select * from table
where (type = 'BUS' and val = 1) or (type = 'FUN' and date is null) or (type ='BUS' and date is null) or (type = 'FUN' and val = 1)

You 3rd condition contradicts with first two conditions.

3.Do not fetch if type is BUS and val=1 and type is FUN and val=1

[baskee]

Thanks for your attempt skhanal. the problem here is we need to consider both the BUS and FUN records while searching and retreive them only if the condition satisfied or we should not retreive either only BUS or FUN.
The following is the requirement given
(a) If val = 1 in both Business
requirements and Functional requirements stages then we should not retrieve those RFS. (num 44 will not be retreived)
(B) If val=1 in Business Requirements and client sign-off is null in the Functional Requirements stage, and val = 0 in the functional requirement stage, we need to retrieve the record. (num 40 will be retrived)
(c) If the val = 0 in Business Requirements and client sign-off is null in the Business requirements stage and val = 1 in the functional requirement stage, we need to retrieve the record.
(num 43 will be retrieved)

so the final output will contain only 40 and 43 .Thanks for your help. if you can help further it is much appreciated.

[baskee]

Thanks for your attempt skhanal. the problem here is we need to consider both the BUS and FUN records while searching and retreive them only if the condition satisfied or we should not retreive either only BUS or FUN.
The following is the requirement given
(a) If val = 1 in both Business
requirements and Functional requirements stages then we should not retrieve those RFS. (num 44 will not be retreived)
(B) If val=1 in Business Requirements and client sign-off is null in the Functional Requirements stage, and val = 0 in the functional requirement stage, we need to retrieve the record. (num 40 will be retrived)
(c) If the val = 0 in Business Requirements and client sign-off is null in the Business requirements stage and val = 1 in the functional requirement stage, we need to retrieve the record.
(num 43 will be retrieved)

so the final output will contain only 40 and 43 .Thanks for your help. if you can help further it is much appreciated.

[skhanal]

You should try grouping the records by id, then use HAVING clause instead of WHERE to filter out unwanted records.