Newbie help on joining tables

[jillybean]

Hi all,

I'm very new to MySQL and am having some trouble figuring out a few things, specifically joining tables. I understand that aliases are needed to use the function properly but for some reason I can't crack this code. Many of the things I'm trying to do are similar to math word problems, which I get severe anxiety over :confused: so it's as if some mental block is put up.

What I'm trying to do is come up with commands to use on a database I have created for a small hospital. I'm GUESSING that I need to join a table to itself but not 100% positive. I listed below the tables that I have in my data base just in case.


List all patients with the same insurer but different doctors.
List all patients with the same last name and same doctor.


All of this information can be found in one table called "Visit." The entries needed are labeled as:

FIRST_NAME (this is patient's first name)
LAST_NAME (this is patient's last name)
DR_NAME (this is patient's doctor name)
INS_NAME (this is patient's insurer name)

This is what I have so far but I know it's wrong:

SELECT F.INS_NAME, F.LAST_NAME, F.DR_NAME, S.INS_NAME, S.LAST_NAME, S.DR_NAME
FROM VISIT F, VISIT S
WHERE F.LAST_NAME = S.LAST_NAME
AND F.DR_NAME < S.DR_NAME
ORDER BY F.LAST_NAME, S.LAST_NAME


Can someone please help me? I know these have GOT to be so simple but for the life of me, I cannot get it to work.

Thank you in advance!

Jill

ETA: I may be wrong in thinking that I need to join a table to itself for this particular case. I'm going to list the tables that I have in my database to see if I could get this another way.

Insurer Table
INS_NUM (primary key)
INS_NAME
STREET
CITY
STATE
ZIP
PHONE

Address table
FIRST_NAME
LAST_NAME
PATIENT_NUM (primary key)
ADDRESS
CITY
STATE
ZIP
PHONE

Patient table
FIRST_NAME
LAST_NAME
PATIENT_NUM (primary key)
DOB
DR_NAME
INS_NUM
BALANCE_DUE

Visit table
FIRST_NAME
LAST_NAME
PATIENT_NUM (primary key)
DATE
DR_NAME
COMPLAINT
CHARGE
AMOUNT_PD
INS_NAME
INS_NUM

[Lee Stevens]

What are the tables names?

You say it can all be found in Visitor table but what the other tables names?

Your SQL query is: FROM VISIT F, VISIT S Why are they both coming form VISIT?

[jillybean]

Table names are in bold in my original post:

Patient
Insurer
Address
Visit

Hope that helps. :D

[Lee Stevens]

Well from your original post here's the answer:

List all patients with the same insurer but different doctors.
SELECT * FROM patient pt WHERE pt.ins_name = 'INSURER NAME' AND pt.dr_name != 'DOCTOR NAME'

List all patients with the same last name and same doctor.
SELECT * FORM patient pt WHERE pt.last_name = 'LAST NAME' AND pt.dr_name = 'DOCTOR NAME'

[jillybean]

I will try that, thanks so much!

[Lee Stevens]

You have to set these:


INSURER NAME
DOCTOR NAME
LAST NAME


Use a form the easiest way.

[Old Pedant]

Well from your original post here's the answer:

List all patients with the same insurer but different doctors.
SELECT * FROM patient pt WHERE pt.ins_name = 'INSURER NAME' AND pt.dr_name != 'DOCTOR NAME'

List all patients with the same last name and same doctor.
SELECT * FORM patient pt WHERE pt.last_name = 'LAST NAME' AND pt.dr_name = 'DOCTOR NAME'

I'm sorry, but neither of those make sense to me.

All that first query will show you are the patients who have a *SINGLE* insurer name and do not have a *SINGLE* doctor name. So if you use "ABC Insurance" as the ins_name, and "JONES" as the doctor name, you will completely miss those patients who use "XYZ Insurance", no matter if they have "JONES" or not.

What you need, instead, is to indeed join the table TO ITSELF, thus:

// List all patients with the same insurer but different doctors.

SELECT A.last_name, A.first_name, A.ins_name, A.dr_name, B.last_name, B.first_name, B.dr_name
FROM patient AS A, patient AS B
WHERE ( A.last_name <> B.last_name OR A.first_name <> B.first_name )
AND A.ins_name = B.ins_name
AND A.dr_name <> B.dr_name
ORDER BY A.last_name, A.first_name, B.last_name, B.first_name

Do you see it?

You join the table back to another copy of itself. For each person in the first occurrence (A) you look for a *different person* in (B) [A.last_name <> B.last_name OR A.first_name <> B.first_name] who has the SAME insurer [A.ins_name = B.ins_name] and a DIFFERENT doctor [A.dr_name <> B.dr_name], just exactly as asked for.

NOW...

We should note that you will thus get each matching person *TWICE*. Example:

Adams, Bob, Aetna, Dr. Ross, Heath, Jane, Dr. Stein
...
Heath, Jane, Aetna, Dr. Stein, Adams, Bob, Dr. Ross

That is a *CORRECT* result, as the question was posed. (It never said "show each result only once.") There's an easy way to avoid these duplicates, if it's important.

**************

As for the second query, it should now be almost obvious:

List all patients with the same last name and same doctor.

SELECT A.last_name, A.first_name, B.first_name, A.dr_name
FROM patient AS A, patient AS B
WHERE A.last_name = B.last_name
AND A.first_name <> B.first_name
AND A.dr_name <> B.dr_name
ORDER BY A.last_name, A.first_name, B.first_name


Hope I don't need to explain that one, as it follows directly from the first one.