Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2008
    Posts
    15

    Red face Unanswered: to insert a data into a table

    The values for the third table to be fetch from t1 and t2
    I want queries for this.
    Table t1
    Eid(primary key not assigned) Entry(time)
    101 101101
    102 111111
    101 121145
    102 121334


    And

    Table t2 as
    eid Exit(time)
    101 111545
    102 153456
    102 164646
    101 171545

    And I want in third table as
    eid entry exit


    The values for the third table to be fetch from t1 and t2
    I want queries for this.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you looked up JOINs in the documentation?
    That's a good place to start
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2008
    Posts
    15
    Quote Originally Posted by georgev
    Have you looked up JOINs in the documentation?
    That's a good place to start
    yes i tried wit join but i get duplicates

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Post what you've tried
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2008
    Posts
    15
    insert into table3 (eid, entry, exit)
    select a.eid, a.entry,b.exit
    from table1 as a, table2 as b
    where a.eid=b.eid


    and the result is

    101 101101 111545
    101 101101 111545
    101 121145 153456
    101 121145 153456

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And what results do you actually want?
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2008
    Posts
    15
    i want the result like this

    eid entry exit
    101 101101 111545
    101 121145 153456

  8. #8
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Quote Originally Posted by varunkrish

    Eid(primary key not assigned) Entry(time)
    101 101101
    102 111111
    101 121145
    102 121334

    Table t2 as
    eid Exit(time)
    101 111545
    102 153456
    102 164646
    101 171545
    Looking at the original data, how do you know which exit time goes with which entry time? There seems to be no logical pattern for eid=102. ie, both exit times for eid=102 are later than both entry times for eid=102. How do you coorelate the entries?

    Is there a reason that you have two seperate tables to begin with, instead of one table like t3?

  9. #9
    Join Date
    Jun 2008
    Posts
    15
    the values are doesn't matter i want to know how the two tables are inserted into the third table

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're desired output, as buckeye234 has pointed out, don't make sense!
    Quote Originally Posted by varunkrish
    T1
    eid entry
    101 101101
    102 111111
    101 121145
    102 121334

    T2
    eid exit
    101 111545
    102 153456
    102 164646
    101 171545

    T3
    eid entry exit
    101 101101 111545
    101 121145 153456
    I can see the logic (sort of) behind the highlighted red lines, but the highlighted blue one makes no sense.

    But based on the information provided thus far, I would say that your table design is flawed.
    George
    Home | Blog

  11. #11
    Join Date
    Jun 2008
    Posts
    15

    Angry

    i want the result as

    eid entry exit
    101 111215 121314
    101 131415 141516


    from the tables

    eid entry
    101 111215
    102 131412
    101 131415


    eid exit
    101 121314
    101 141516


    this is the table there is no fault in this table

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How do you know which row from t1 matches with that in t2?
    Both have eid = 101; but how do you decide which goes with which?
    George
    Home | Blog

  13. #13
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Quote Originally Posted by varunkrish
    the values are doesn't matter i want to know how the two tables are inserted into the third table
    Ah, but the values do matter. Your previous SQL statement takes all of the data from T1 and joins it with all of the data from T2 where the eids are the same. For eid=101 you have two rows in T1 and two rows in T2, if you join these together, you get a four row result set. However, you indicated that you want a two row result set. To acheive this, you need to join one row in T1 with one row in T2.

    As George and I have asked, what logic do you use to decide which row in T2 gets paired to which row in T1. The biggest part of the battle is clearly articulating the logic behind choosing wich rows make a pair. Once you have done that, it's just a matter of writing the SQL statement to implement that logic. Does that make sense?

  14. #14
    Join Date
    Jun 2008
    Posts
    15
    thnks for ur reply

    i insert the values in thrid table i get the results as

    eid enter exit
    101 111215 NULL
    101 NULL 121314
    101 131415 NULL
    101 NULL 141516

    i want to minus the first entry time with the exit time , but it is impossiable,
    u hav an idea ,
    i am working for a time management to calucate how hours employee worked in the office for this the difference is imp

    waiting for ur reply

  15. #15
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    OK, now we may be getting somewhere. Let's assume, for this discussion, that all eid's for the entries being discussed are the same, say 101. Let's also assume that there are two rows in the entry table, referred to as entry1 and entry2, and two rows in the exit table, referred to as exit1 and exit2. Let's also assume that the first row in each table is at a time that is earlier than the second row, for example, entry1 < than entry2.

    Given this scenario as a starting point:
    1. Does this represent the real world data?
    2. Can you gaurantee that entry1 < exit1 and exit1 < entry2 and entry2 < exit2?
    3. If that is true, can you say the same is true for any number of rows in the entry table and exit table (Again, assuming the same eid for the rows being considered)?

    Asnwer these questions and we'll continue from there.

Posting Permissions

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