Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2007
    Posts
    26

    Unanswered: is it efficient to insert several table for executing 1 process?

    hi..

    sorry, if i ask a simple question but it quite confusing me anyway..
    so i have 3 table..
    MR(MRId->pk, regDate)
    patient(patientId->pk, fname, lname)
    havepatient (MRId ->pk, patientId->pk)

    my question is, if i want to add new MR, in which 1 MR can has several patients, it means that i have to execute3 different insert commands for both these 3 tables?

    please reply me as soon as possible =)
    thx for your attention

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Firstly, do not apologise for asking a "simple question"
    Secondly, this is not a simple question!

    Why can't you create a MR without any patients "attached" to it?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2007
    Posts
    26
    Hi, thank's for the fast reply.. =)

    "Why can't you create a MR without any patients "attached" to it?"
    MR is like an identification for each familiy, and each familiy consist of several patient..
    does my erd is already correct? and if, for example, i want to add a new MR with new patients of course, so i have to do :

    insert into havepatient(MRId, patientId) values (NULL, NULL);
    insert into MR(MRId, regDate) values (1, 2007/04/05);
    insert into patient(patientId, fname, lname) values(1, lala, baba);

    is it correct? and is it efficient to perform 3 inserts just for adding 1 new MR?

    thanks for your attention

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Personally I'd advise you create the patients and MRs BEFORE you join them in your havepatient table
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2007
    Posts
    26
    Hi...

    yeah.. i should entered those 2 first..
    thank's for your replies..

  6. #6
    Join Date
    Feb 2007
    Posts
    26
    Hi..
    sorry for asking the same problem againn..
    so, i write something like this:

    Code:
    $queryMR= "INSERT INTO MR (MRId, time) values (NULL, current_timestamp())";
      $resultMR= safe_query($queryMR);
    
    $queryPatient= "INSERT INTO patient ( patientId , fname ,lname)";
      $queryPatient.= "VALUES (NULL , '$firstname', '$lastname')";
      $resultPatient= safe_query($queryPatient);
    
    $queryHave_Relation= "INSERT INTO havePatient(patientId, MRId) SELECT p.patientId, m.MRId";
      $queryHave_Relation.= "FROM patient p, MR m WHERE m.MRId='' and (p.fname= '$firstname' and p.lname= '$lastname')";
      $resultHave_Relation= safe_query($queryHave_Relation);
    my question is what should i put in the MRId= '' (the red one), i want to take the result of MRId from $resultMR, but if i put the MRId straightaway (hardcode) then it is useless.. i have thought about taking the latest MRId from the MR table, but the problem is there are more than 1 user that perform insertion in the same time.

    can anybody help me out from this problem? =)
    thank's for ur attention..

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    One thing I noticed straight away is that you're insering a NULL value as your MRId!! You should NOT have null values for an identity field like this - and as your patientId! If these are autonumbers you can simply ignore them in an insert as they will be populated automatically.

    Secondl; according to your 3rd query you are searching for patients' first names and last names as a means of joining the two... Bad move...
    What if you have 10 people called John Smith?
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2007
    Posts
    26
    should i use the combination of fname, lname, and DOB instead? i think it would be better if i could take the patientId from $queryPatient straightaway as the input for pID in $queryHave_Relation..

    and what do you think would be the best for MRId?
    thankss a lot..

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    INSERT
    INTO patient(fname, lname) values('John', 'Smith', '01/01/1980 00:00:00.000');
    
    INSERT
    INTO patient(fname, lname) values('John', 'Smith', '01/01/1980 00:00:00.000');
    
    INSERT
    INTO patient(fname, lname) values('John', 'Smith', '01/01/1980 00:00:00.000');
    There are now 3 John Smiths who happen to be born on the same day...
    The whole point in an identity (key) field is to uniquely identify a record.
    George
    Home | Blog

  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Also, after inserting a record, (for SQL Server) you may get the value of your most recently inserted value by using the @@identity function

    Code:
    INSERT INTO MR (time) values (current_timestamp())
    
    SELECT TOP 1 @@identity as LastRecordID from MR
    This will return the last identity value in your connection. Even if another users inserts a record after you've inserted the record, but before you select @@identity, the value you receive is the value associated with your insert.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Wow, I never knew you could do that...
    Just a quickie on ths above loquin...
    I ran the following in query Analyzer
    Code:
    SELECT TOP 1 @@identity AS LastRecordID FROM employee
    Which gave me this error
    Code:
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near '1'.
    I'm running this on SQL Server 2k - I've never used a TOP statement in SS before and it appears that it's not liking them. Any ideas?

    EDIT: Just ran this on a SS 2K5 database... the TOP worked, but it returns NULL every try so far..
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Back to the original problem:
    Tenna, what database are you using and what is the key field in each table (autonumber?) etc.
    George
    Home | Blog

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    who said the PKs had to be identity/autonumber columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I just assumed that "patientId" was an identity PK, same goes for MRId
    also why I asked the question too
    *shrugs*
    George
    Home | Blog

  15. #15
    Join Date
    Feb 2007
    Posts
    26
    Hi..
    thx for the replies..
    i just read the forum..
    thxx..
    regards,

    Lena

Posting Permissions

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