Select query for two table into one.....

[poojavb]

Too weird but still I have a doubt....

I have two table....
-> OutpatientDetails
-> InpatientDetails

But the fieldnames are same except for the ID....i.e. OutPatientID and InPatientID respectively.....

I want to combine both the tables and produce one output.....I dont know whether it is possible or no....

I want the doctors to see both the data simulataneously...

like

PatientID | PatientFName | PatientLName | Gender
------------------------------------------------
INP1      | ABC          | XYZ          | Female    -this is an inpatient
OTP1      | DEF          | PQR          | Male      -this is an outpatient

Is it possible to get the query this way.....

do let me know....

Thanks in advance.....

[debasisdas]

OutpatientDetails should be a subset of InpatientDetails.

Technically only the patients how have come in can only go out.

You need to maintain the same id in both the tables.

Later if it is required you need to join both the tables based on the ID field for any reporting or any other purpose.

[ChrisHunter]

This should do i i think, let me know if it works or not.

SELECT INP1.PatientID, PatientFName, PatientName, Gender
FROM INP1 LEFT JOIN OTP1
ON
INP1.PatientID = OTP1.PatientID;

[poojavb]

No.....Inpatient means that patient who will have a stay in the hospital....

outpatient means just have come for testing and leaves the same day....

both are different.....

ChrisHunter - join cannot be applied as the ID's are different...it wont be same anytime.....both the inpatient and outpatient are different....

[ChrisHunter]

You should have all patients in a single table with a unique ID for each patient and then seperate tables (in-patient and out-patient) that reference the patient's record from the main patient table.

Unfortunatly other than the below query (which will return all rows from both tables and show which will show both matched and unmatched patient records) i don't know the query to fix the issue correctly.

SELECT OP.PatientID IP.PatientID OP.PatientFName, OP.PatientLName, OP.Gender
FROM OutpatientDetails AS OP
FULL OUTER JOIN InpaitentDetails AS IP
ON OP.PatientFName = IP.PatientFName 
AND OP.PatientLName = IP.PatientLName 
AND OP.Gender = IP.Gender;

Hope this helps, let me know how it goes . . .

[anand01]

@poojavb ,
It sounds like the patient who comes for check up is outpatient , if necessary he will admit as inpatient so you can have common ID and status (which tells inpatient or Outpatient).

Still you want to have seperate id then u can add doctors id in both the tables the you can get complete list.

[JorgeM]

I would also suggest that you have one patient table. Within the patient table. Then you can have a seperate inpatient table and another outpatient table that contains the ID from the patient table. In the inpatient and outpatient tables, you would just have details related to the in or out event. The reason being is that today, you may have a patient that registers for an inpatient procedure. Next month, this patient has a complication and comes back to the facility and is admitted as an inpatient. You wouldnt create a new patient record. You already have the patient information (name, address, etc...). You would just create a record in the inpatient table. Of course, this is just the concept, the details would depend on other factors. The goal is is to normalize the data.

[BitBlt]

If I'm not badly mistaken, it appears you guys are all missing the OP's point here.

If I may make an assumption or two here, it appears that there is no "patient" table...there are TWO "patient" tables...one for InPatients and one for OutPatients. And, they don't relate to each other at all. Not saying this is a great design, just saying that's what the OP said he had to work with.

So, shouldn't a simple "UNION" query satisfy the OP's request?

Select InPatientId as 'PatientID', PatientFName, PatientLName, Gender
from InPatient
UNION ALL
Select OutPatientId as 'PatientID', PatientFName, PatientLName, Gender
from OutPatient

I hope this helps. If my assumptions are incorrect, I apologize.

[JorgeM]

I can only speak for myself...I did understand what the OP was asking, but failed to provide a solution such as you suggested. I agree and support your answer. The UNION will "merge" the results from two Select statements very well.

My suggestion to Poojavb is to consider redesigning the structure of the database to better normalize the data and to consider some of the points I touched upon in my previous post.

[poojavb]

BitBlt - U are just superb....thanks a lot....

let me tell u all in brief....actually I did not go for one table since if anyone opens the database then it is difficult to get the outpatient and inpatient.....

So I made two different table for inpatient and outpatient....so that it is easy to find the data.....it may be stupidity but I had to do it .... :)