Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Joining two unrelated tables

    I have 2 tables one is a active and one is a inactive. The information is moved from the active table to the inactive table once the patient becomes inactive. If I'm searching for John Johnson I have to search first in the active and then do a search in the inactive. Is there a way to combine my active and inactive queries? so if I'm looking for John if will show up rather he is active or inactive.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I appears your answer is a UNION query - as long as the tables have the same structure that should give you all of the records in both tables.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Findlay, Ohio
    Posts
    57
    Thanks
    4
    Thanked 6 Times in 6 Posts
    As WendllB said: UNION is the correct answer. Too tell which table it came from add another field. i.e.
    select name,address,city, "a" as which
    from table1
    UNION
    select name,address,city, "i" as which
    from table2

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Another way is to use just one table for both active and inactive records, you will need another field to distinguish between the two. Makes querying easier.

  5. #5
    New Lounger
    Join Date
    Jan 2015
    Posts
    24
    Thanks
    0
    Thanked 3 Times in 3 Posts
    One table also makes for better performance since it's faster to simply flag a patient record as inactive rather than move that record to a different table.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    A point against the 2 table approach is what happens if inactivity is flagged incorrectly, it's far easier to unflag records as against transferring these records back again.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    561
    Thanks
    51
    Thanked 68 Times in 66 Posts
    Quote Originally Posted by orangehat View Post
    As WendllB said: UNION is the correct answer. Too tell which table it came from add another field. i.e.
    select name,address,city, "a" as which
    from table1
    UNION
    select name,address,city, "i" as which
    from table2
    Great answer. This solution hints at one important matter. The underlying tables do not need to have identical structure, only the final union set. Archive tables often have some additional fields in them, for instance a datestamp to indicate when the record was archived.

    The archive table design pattern is fairly popular and there's nothing wrong with it. The main downside of an archive table design relates directly to the original question: How do you present a unified view of all your data when you want to? By the way, a View is one mechanism to do exactly this. Balanced against this are the benefits of increased overall performance, and a logical segregation of active versus inactive information.

    In fact all variations on correct schema design, i.e. compliant with Third Normal Form, involves tradeoffs. Typically you can defer or reassign necessary computational activity and disk I/O. However the piper must be paid eventually. Most schema designs optimize for routine activity performed every day by users.

    The OP did not specify but often these systems come from a third party. Therefore it may not be possible or appropriate for the OP to modify the table design. Making schema adjustments to any software has oodles of ramifications to the code built on top of that and is not for the faint-hearted!

  8. #8
    New Lounger
    Join Date
    Feb 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Single table is better

    Quote Originally Posted by patt View Post
    A point against the 2 table approach is what happens if inactivity is flagged incorrectly, it's far easier to unflag records as against transferring these records back again.
    I don't think this is much of an issue. You can easily make it more difficult to accidentally flag/unflag the field by using a form to view the data (which is really a much better way to view the data anyway.) In the form, just add an 'event' to the particular field control (i.e. the "Active" field), which will open a message box saying something like "are you sure you want to change this to Active/Inactive", along with Yes/No buttons.

Posting Permissions

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