How should this table be related? ? ?

huhejun

Form A: Drug Information (ypid ypname ...)
Form B: Diagnosis Information (zlid zlname ...)
A, B are the two major categories of medical expenses
Form C: Cost Information (Time Medical Number Expense Classification ypid or zlid ...)

Question: How to correlate ypid or zlid in table C with information such as ypname? (It is necessary to determine the expense category first, then the association. Can this be achieved? If you cannot just add the table C field, fix it)

unkmass

Theoretically, table A and table B should be foreign key tables of C, which means that table C should have two fields ypid and zlid associated with tables A and B, respectively.

unkmass

Sorry, said the other way.
In theory, C should be a foreign key table for tables A and B, that is to say, table C should have two fields ypid and zlid associated with tables A and B, respectively.

huhejun

In this case, it is estimated that it is not easy to connect. Is there any other way to provide it? Thank youunkmass!

unkmass

If you can guarantee that the ypid of table A and the zlid content of table B do not overlap with each other, this can also be used, but there are disadvantages in the design.

select A.ypname, C. Expense classification, C. Time, C. Visit number from A, C where A.ypid = C. Expense classification and C. Expense classification in (select ypid from A);

select B.zlname, C. Expense classification, C. Time, C. Visit number from B, C where B.zlid = C. Expense classification and C. Expense classification in (select zlid from B);

saoshe

Depending on what database you use, I am familiar with oracle, and give you the implementation of oracle sql. The implementation mainly uses the decode function
select ... decode (expense classification, A, ypid, zlid), decode (expense classification, A, ypname, zlname)
To explain: the decode function is a conditional statement in SQL. If "Expense Classification" = "A", then ypid, otherwise zlid. Others are similar. Hope that helps. If the database is not using oracle, you can find similar functions implemented by your database.

aimar7357

Add a field, add a problem and everything is solved. But it may be a bit more troublesome on the page.

unkmass

select A.ypname as expense name, C. expense classification, C. time, C. doctor number from A, C where A.ypid = C. expense classification and C. expense classification in (select ypid from A) union select B. zlname as expense name, C. expense classification, C. time, C. doctor number from B, C where B.zlid = C. expense classification and C. expense classification in (select zlid from B);

unkmass

MS SQL.

88216851

Two strong men