2

Each date as one table in my database.In all Tables data is same but table is different. I want to search one column (this column is common for all tables).I passed dates like 2015-01-01 as from date and 2015-05-31 as to Date

I wrote the query like this but this is not working for me.

select MsgId, UserId, Priority, MsgStatus, 

FROM abc.`2015-05-04` as p1,abc.`2015-05-14` as p2 
WHERE  Id="cfcbc375-372e-4586-bc23-9e94e6eb2c16"
Improve this question
10
  • 2
    the table names are dates? Commented Dec 22, 2015 at 11:20
  • 1
    Is this table name abc.2015-05-04 , abc.2015-05-14 ? Commented Dec 22, 2015 at 11:23
  • 3
    God, that's the worst idea I have seen. You should simply create a column on your main table with the dates, instead of making a table for each day since the columns are the same in every table. It would make it easier to gather the information between dates. Commented Dec 22, 2015 at 11:24
  • 4
    If you will excuse me saying, I think your problem is a bad database design. Commented Dec 22, 2015 at 11:24
  • First time i see dates on a table name. Commented Dec 22, 2015 at 11:28

4 Answers 4

Reset to default
1

to search for a date use to_date

and >= to_date('22-12-2015','dd-mon-yyyy')
and <= to_date('21-01-2016','dd-mon-yyyy')

this would look for all instances where the date was between 22nd December and 21st January inclusive.

This is assuming that OP is misunderstanding the FROM part of an SQL statement, the dates should be in the WHERE/AND clauses.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You can achieve with append functionality Like this, Here we are taking from date to till date and after that converting date type to string format. now calling sql query till last date then use append functionality like finalQuery = finalQuery + " " + " union all " + " " + query1; Hope You will get data from start date to end date. :)

finalQuery = "SELECT  TransDate FROM `" + str_date + "` WHERE `Id`='" + Id 

for(int i=1;i<dates.size();i++){
   Date lDate =(Date)dates.get(i);
   ds = formatter.format(lDate);    


  for(int i=0;i<s;i++) {
      query1 = "SELECT  TransDate FROM `" + ds + "` WHERE `Id`='" + Id + "'";         
  }
  finalQuery = finalQuery + " " + " union all " + " " + query1;
}
Improve this answer

1 Comment

yup now i'm getting. Thanks
0

You can use UNION ALL 

select MsgId, UserId, Priority, MsgStatus, 
       DestinationNumber, OrginatorName, MsgSubmitID,
       MsgStatusMsg, TransDate, ClientMsgId 
FROM abc.`2015-05-04` as p1
WHERE  Id="cfcbc375-372e-4586-bc23-9e94e6eb2c16"

UNION ALL

select MsgId, UserId, Priority, MsgStatus, 
       DestinationNumber, OrginatorName, MsgSubmitID,
       MsgStatusMsg, TransDate, ClientMsgId 
FROM abc.`2015-05-14` as p2 
WHERE  Id="cfcbc375-372e-4586-bc23-9e94e6eb2c16"

you should use union all for all table between 2015-05-04 and abc.2015-05-14 like 2015-05-04, 2015-05-05, 2015-05-06 and so on

Improve this answer

Comments

0

I see. You have one table for each date, every table contains the same columns.

That's not how you design tables, it's bad programming.

You must create a new column in one table and add the dates into this column.

Then you can use it as: 

select MsgId, UserId, Priority, MsgStatus, 
       DestinationNumber, OrginatorName, MsgSubmitID,
       MsgStatusMsg, TransDate, ClientMsgId 
FROM abc.`table`
WHERE  Id="cfcbc375-372e-4586-bc23-9e94e6eb2c16" AND
Date BETWEEN 'date1' and 'date2'

See Between operator

Improve this answer

1 Comment

table name as date yar(Daily Dates like 2015-12-22,2015-12-23,2015-22-24)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.