Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Nov 2010
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Exclamation Need help with table joins!

    Hello all. I'm new to this forum and have only come into contact with SQL for around 3 weeks only but anyway... I'm currently interning in a company and I really, really need some help with a query!

    I am told to generate a monthly report. And everyday, the database will generate a new table that is named like "LOG20101001" ; Meaning 2010/10/1 (YYYMMDD).

    So in order to generate a report for the month of October, I will have to get the result from between table LOG20101001 all the way to table LOG20101031.

    I only know simple joins with regards to 2-4 tables but not that many! I was told to use a Stored Procedure. But with stored procedure I have yet another question.

    I used a parameter in Crystal Report to have the user to decide the date they wish to see for a particular report. So it'll be something like :

    SELECT * FROM LOG{?Date};

    But with a stored procedure am I able to declare something similar to this too?

    Sorry if it sounds confusing! I myself is confused as well.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Maybe you should intern at another company. Clearly the people designing the DB at that company are idiots.

    Creating a separate table for each DAY is just plain NUTSO!!!! It's debatable if you should even create one per month, but one per DAY is a horrible DB design.

    ANYWAY....

    You do *NOT* need or want a JOIN. You need a UNION.

    Code:
    SELECT list, of, fields FROM LOG20101001
    UNION ALL
    SELECT list, of, fields FROM LOG20101002
    UNION ALL
    SELECT list, of, fields FROM LOG20101003
    ...
    UNION ALL
    SELECT list, of, fields FROM LOG20101031
    ORDER BY ...
    The ORDER BY applies to the union of all the tables, so you only need it once at the end. On the other hand, if you need to use a WHERE clause in the SELECTs, you'll probably get a lot more efficiency putting it separately on each table. (If you want to apply it to the UNION, the syntax is a bit different.)

    You mentioned Crystal Reports, so I"ve assumed that you are using SQL Server. Which is why I used UNION ALL. Not all DBs support UNION ALL, but generally the ones that don't will simply use UNION (alone) to mean the same thing. (Without UNION ALL, SQL Server will automatically remove any duplicated records...which is surely not what you would want in working with logs.)

    NOTE: If the various LOG201010xx tables do not include the date as one of their data fields--if you are supposed to imply it from the name of the table--you can add it into the SELECTs easily, thus:
    [code]
    Code:
    SELECT '10/1/2010' AS logDate, list, of, fields FROM LOG20101001
    UNION ALL
    SELECT '10/2/2010' AS logDate, list, of, fields FROM LOG20101002
    UNION ALL
    SELECT '10/3/2010' AS logDate, list, of, fields FROM LOG20101003
    ...
    UNION ALL
    SELECT '10/31/2010' AS logDate, list, of, fields FROM LOG20101031
    ORDER BY ...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    By the way, to create a stored procedure that simply takes in the month and year and generates the UNION is not terribly hard, but it doesn't sound like a project for a newbie intern. In any case, I'm not going to talk about it without knowing what database system you are using, as Stored Procedure syntax varies *SO* much from DB to DB.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Nov 2010
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you very much for the reply!

    And yes. Its INSANE to generate one table a day.
    I need to use a "WHERE" clauses. But do I have to do it for every SELECT?

    I'm also seeking help from my friend too! As for Crystal Report, totally new to that. But i am able to generate simple daily report using queries and parameters, the only HUGE problem I have here is this.

  • #5
    New to the CF scene
    Join Date
    Nov 2010
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Oh and, I have something to clarify too;

    My current SQL for ONE daily report is as followed :
    Code:
    select TERM_ID, T_CODE, MSG_TYPE , RESP_CODE,
    (select corp_name from corp where corp_bankid = ACQ_ID),
    ACQ_ID , MERCH_ID, MERCH_NAME from MATRXNLOG{?Date} 
    where T_CODE in ('91190', '92190') and MSG_TYPE = '2110' and RESP_CODE = '000'
    
    union all
    
    select TERM_ID, T_CODE, MSG_TYPE , RESP_CODE,
    (select corp_name from corp where corp_bankid = ACQ_ID),
    ACQ_ID , MERCH_ID, MERCH_NAME from MATRXNLOG{?Date} 
    where T_CODE in ('91191', '92191') and MSG_TYPE = '2110' and RESP_CODE = '000'
    
    union all
    
    select TERM_ID, T_CODE, MSG_TYPE , RESP_CODE,
    (select corp_name from corp where corp_bankid = ACQ_ID),
    ACQ_ID , MERCH_ID, MERCH_NAME from MATRXNLOG{?Date} 
    where MSG_TYPE = '2430' and RESP_CODE = '880'
    The first SELECT being "Issued Cheque", followed by "Rendered Cheque" and "Voided Cheque" because I have to specify in my Report the COUNT of each different Cheque.

    ..And I have to do a monthly report with regards to this! and hence my dilemma.
    Last edited by astreal; 11-08-2010 at 07:07 AM.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Sorry I missed your post last week. Did you resolve this?

    You never did say what DB you are using.

    I don't see why you need a UNION for that query.

    What's wrong with:
    Code:
    select TERM_ID, T_CODE, MSG_TYPE , RESP_CODE,
    (select corp_name from corp where corp_bankid = ACQ_ID),
    ACQ_ID , MERCH_ID, MERCH_NAME from MATRXNLOG{?Date} 
    WHERE
        ( T_CODE in ('91190','92190','91191','93191') and MSG_TYPE = '2110' and RESP_CODE = '000' )
    OR
        ( MSG_TYPE = '2430' and RESP_CODE = '880' )
    ????

    Yes, you'd need to repeat the WHERE for each table. Ready to shoot the idiot who designed that database?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    astreal (11-19-2010)

  • #7
    New to the CF scene
    Join Date
    Nov 2010
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Sorry I missed your post last week. Did you resolve this?

    You never did say what DB you are using.

    I don't see why you need a UNION for that query.

    What's wrong with:
    Code:
    select TERM_ID, T_CODE, MSG_TYPE , RESP_CODE,
    (select corp_name from corp where corp_bankid = ACQ_ID),
    ACQ_ID , MERCH_ID, MERCH_NAME from MATRXNLOG{?Date} 
    WHERE
        ( T_CODE in ('91190','92190','91191','93191') and MSG_TYPE = '2110' and RESP_CODE = '000' )
    OR
        ( MSG_TYPE = '2430' and RESP_CODE = '880' )
    ????

    Yes, you'd need to repeat the WHERE for each table. Ready to shoot the idiot who designed that database?
    Sorry about that! I wasn't able to get onto this forum because I was summoned to somewhere else. But I already resolved this!

    I'm using SQL Server 2005. My friend taught me stored procedure and import them into a local temporary table.
    And thanks for simplifying the codes for me!


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •