Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    5

    Question Unanswered: Show the rows of a table side by side

    Hi Everybody,
    I am new here. I have a table named Jrn with fields as follows
    Jr_id numeric
    Jr_date datetime
    Jr_Tr_id numeric (foreign key to another table Trn with field tr_id )
    Jr_lm_id numeric (foreign key to another table Lmn with field Lm_id )
    Jr_amount currency


    Design of Table "Trn"
    Tr_id numeric
    Tr_name varchar

    Example values

    Tr_id Tr_name

    1 Receipt
    2 Payment

    Design of Table "Lmn"

    lm_id numeric
    lm_name varchar

    Example values

    Lm_id Tr_name

    1 Sam
    2 Das
    3 Babu
    4 Lal


    Values in the table Jrn will be as follows

    Jr_id Jr_date Jr_Tr_id Jr_lm_id Jr_amount
    1 01-04-09 1 1 10000
    2 01-04-09 2 3 5000
    3 03-04-09 2 2 25000
    4 03-04-09 1 1 20000
    5 03-04-09 1 4 12000
    6 04-04-09 2 4 7500
    7 05-04-09 1 3 50000


    I would like to get the results as follows

    Date Type Name Amount Date Type Name Amount

    01-04-09 Receipt Sam 10000 01-04-09 Payment Babu 5000
    03-04-09 Receipt Sam 20000 03-04-09 Payment Das 25000
    03-04-09 Receipt Lal 12000 04-04-09 Payment Lal 7500
    05-04-09 Receipt Babu 50000

    No of payments and receipts may vary

    If any body knows how to do it using a single query or view or joining two views
    Remember I am using Sql Server 2000.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    sirajul, You need something that can tie (join) the Receipt and Payment row together. Or, to put it another way, how do you know which Receipts and Payments are the same so they get put on the same row?

    Using your examples.

    Jrn will be as follows

    The first output row:

    Date Type Name Amount Date Type Name Amount
    01-04-09 Receipt Sam 10000 01-04-09 Payment Babu 5000

    consist of the JR_ID 1 and 2:

    Jr_id Jr_date Jr_Tr_id Jr_lm_id Jr_amount
    1 01-04-09 1 1 10000
    2 01-04-09 2 3 5000

    The only thing these rows have in common is the JR_DATE.

    The second output row:

    Date Type Name Amount Date Type Name Amount
    03-04-09 Receipt Sam 20000 03-04-09 Payment Das 25000

    consists of JR_ID 3 and 4:

    Jr_id Jr_date Jr_Tr_id Jr_lm_id Jr_amount
    3 03-04-09 2 2 25000
    4 03-04-09 1 1 20000

    The only thing these rows have in common, again, is the JR_DATE. So far, so good (as long as the same person doesn't make the same transaction on the same date).

    However, the third output row:

    Date Type Name Amount Date Type Name Amount
    03-04-09 Receipt Lal 12000 04-04-09 Payment Lal 7500

    consists of JR_ID 5 and 6:

    Jr_id Jr_date Jr_Tr_id Jr_lm_id Jr_amount
    5 03-04-09 1 4 12000
    6 04-04-09 2 4 7500

    The only thing these rows have in common is the JR_LM_ID. However none of the other output rows have this in common (and the fact Lal was the person of both Receipt and Payment isn't a good join condition).

    The fourth output row:

    Date Type Name Amount Date Type Name Amount
    05-04-09 2Receipt 3 Babu 50000

    Consists of JR_ID 7:

    Jr_id Jr_date Jr_Tr_id Jr_lm_id Jr_amount
    7 05-04-09 1 3 50000

    The fact it does not have a matching Payment row is not a problem but doesn't give a clue about any common value.

    To summarize, 2 of the output rows have only JR_DATE in common and 1 or the output rows has JR_LM_ID in common. You need something that is the same for all rows so you can join the correct Receipt and Payment rows together.

    Something like Transaction ID or Invoice Number maybe.

  3. #3
    Join Date
    May 2009
    Posts
    5
    Thanks Stealth!!!
    Sorry for the delay in replaying.
    Following your reply I added a new column in the table which will have same entries and went for full outer join on the newly added column and it worked.

    Thanks again

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can I ask - do you really name your columns <table_name>_<fieldname>?

    Pretty redundant (not to mention annoying when writing queries), don't you think?
    George
    Home | Blog

Posting Permissions

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