Joining two unrelated tables

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote