Hi ,

Could you please help me building a query with the below requirements.

Table1 has got 5 columns.
(PK)col1 col2 col3 col4 col5
1 a b c d
2 e f h
3 I k l
4 m n o p
5 q r s t

I need to have one single query where in i would have to compare compare against all the columns, if a value is null then still that row should be be fetched. like

select * from table1 where col1=1 and/or col2=a and/or col3=b and/or col4=c and/or col5=""

the first row should be displayed. like if one value is missing then that value has to be ignored and others has to be conisidered. I know i can do that with the programming logic. But i need to find out a way doing it with a sql query.

I am not sure I understand what you want to return. Could you be more specific and supply some examples of what you expect to see from what you have?

Andy

If I got this right (pending the requested explanation), I think you just have to do this:

SELECT ...
FROM ...
WHERE col1 = 1 AND ( col2 IS NULL OR col2 = ... ) AND ( col3 IS NULL OR col3 = ... ) AND ...

p.s: Read up on the semantics of NULL and how it is used in predicates.

I do not think that that is what is desired. But if it is, then ther is even an easier way:

select * from table1 where col1 = 1 and coalesce(col2,'a') = 'a' and coalesce(col3,'b') = 'b' and ...

Andy

like if one value is missing then that value has to be ignored and others has to be conisidered.............?????

Do you mean to say if the value is missing or mismatching then that column should be eliminated from the result set...? (** dont think this is a poor question, just need to confirm what you need)

OR
Did you mean to say that particular row should be eliminated from the result set..?

Is this query STATIC (will you embedd in program ..?)