0

I have a table which have multiple columns. Say, the columns are C1, C2, C3 and C4.

C1 contains daytime, 
C2 contains names,
C3 is the numeric values and 
C4 have 2 values repeatedly say a and b.

Now I need to query data in a way that for daytime, the value present in C3 corresponding to a.C4 shall be returned. If C3 doesn't have a value for a.C4 it should give me value in C3 for b.C4.

Ferdinand.kraft
  • 12,579
  • 10
  • 47
  • 69
user2563162
  • 1
  • Sorry, I do not understand, can you give an example with source data, and result data?. For example, i do not understand why you can have two values in same column C4? Thanks. – Gaston Flores Jul 10 '13 at 14:36

1 Answers1

0

Does your data look something like this:

Jan12, Tom, 1, a
Jan12, Tom, 2, b
Jan13, Bob, 3, a
Jan14, Sue, 4, b

I think you saying that you want to run (Query 1):

SELECT C3 from TBL WHERE C1=<Datetime> and C4='a' and C3 is not null

However, if no record is found, you want to then run (Query 2):

SELECT C3 from TBL WHERE C1=<Datetime> and C4='b'

If that your objective, consider the second query. To leave out anything that would have returned a record via the first query, you could do this (note modified second query only) (Query 2A):

SELECT C3 from TBL WHERE C1=<Datetime> and C4='b'
   and NOT EXISTS (SELECT * from TBL WHERE C1=<Datetime> and C4='a')

So, you now have two queries, with mutually-exclusive results. You can simply UNION them together and run them as one (Final, combined query).

SELECT C3 from TBL WHERE C1=<Datetime> and C4='a' and C3 is not null
UNION ALL
SELECT C3 from TBL WHERE C1=<Datetime> and C4='b'
   and NOT EXISTS (SELECT * from TBL WHERE C1=<Datetime> and C4='a')
Darius X.
  • 2,886
  • 4
  • 24
  • 51
  • Hi, Yes that's what is required. But I am not getting result as desired. It is providing me values from C3 for C4 = a and is not going to C4 = b even when value in C3 for C4 = a is empty. – user2563162 Jul 11 '13 at 06:57
  • So, what you're saying is that you also want to exclude anything from "Query 1" if C3 is "empty". By "empty" do you mean null? Assuming this is what you mean, I've changed the query above. If you understand principle behind it, you should be able to tweak it to do what you want. – Darius X. Jul 11 '13 at 13:31