12 Replies - 763 Views - Last Post: 27 September 2013 - 05:24 AM Rate Topic: -----

#1 C# Novice  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 26-September 13

Help! Access SQL coding

Posted 26 September 2013 - 05:57 PM

First off I'm new to SQL and coding in general...this is my first official post, HEY EVERONE!! Anyway I am an Information Systems major and really need help on alot of things so I will be on here frequently and will ask for help and try to help anyone who needs help as well.
I am trying to work with SQL in MS Access and am trying to do the following:

1. List the patients who do not have a prescription. The list should show the patient's name, gender, age, admission date, and illness description.
[Use combination of Join and nested sub query]


Here is my SQL..which is almost correct


SELECT Last_name, First_name, Gender, Age, Admit_date
FROM Patient
WHERE NOT EXISTS
(SELECT *
FROM Prescription
WHERE Patient.Patient_no = Prescription.Patient_no); 


The problem is that if i try to add Illness des, i end up with error and do not know how to reference it in my table..WHAT AM I DOING WRONG??

Here is my second problem:
2. Produce a list of patients who were prescribed 'Penicillin'. The list should contain the patient name, medication name, dosage, illness description, and the doctor's name.

My code:
SELECT DISTINCT Last_name, First_name, Medication_desc, Dosage, Illness_desc, Physician_ID
FROM Patient AS P, Diagnosis AS D, Illness AS I, Prescription AS E, Medication AS M, Physician AS Y, Assignment AS A
WHERE (M.Medication_code=E.Medication_code) And (E.Patient_no=P.Patient_no) And (P.Patient_no=A.Patient_no) And (A.Physician_ID=Y.Physician_ID) And (M.Medication_desc="Penicillin");


This query will not even run. What's he problem??

Is This A Good Question/Topic? 0
  • +

Replies To: Help! Access SQL coding

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9195
  • View blog
  • Posts: 34,517
  • Joined: 12-June 08

Re: Help! Access SQL coding

Posted 26 September 2013 - 06:06 PM

Quote

The problem is that if i try to add Illness des, i end up with error and do not know how to reference it in my table..WHAT AM I DOING WRONG??

The error is always helpful.

Quote

2. Produce a list of patients who were prescribed 'Penicillin'. The list should contain the patient name, medication name, dosage, illness description, and the doctor's name.

Why are you not using a join?
Was This Post Helpful? 0
  • +
  • -

#3 C# Novice  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 26-September 13

Re: Help! Access SQL coding

Posted 26 September 2013 - 06:15 PM

Again I am new to SQL and is just learing about joins but i will try to use a join (honestly I thought I was using a join lol). Could you please give me an example of a join. Also, in regards to the first question. The query asks me to enter a parameter for illness_desc, but whenever I add Illness table to the query, it says that there is something wrong with the NOT EXISTS entry...it does not like that Illness_desc is apart of the query without a the table. Could you explain to me why it does not let me add another table when NOT EXISTS is in there? Thanks for the reply also.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9195
  • View blog
  • Posts: 34,517
  • Joined: 12-June 08

Re: Help! Access SQL coding

Posted 26 September 2013 - 06:18 PM

Regarding joins:
http://technet.micro...=sql.80%29.aspx
http://msdn.microsof...y/zt8wzxy4.aspx
http://www.codinghor...-sql-joins.html

Quote

Also, in regards to the first question. The query asks me to enter a parameter for illness_desc, but whenever I add Illness table to the query, it says that there is something wrong with the NOT EXISTS entry...it does not like that Illness_desc is apart of the query without a the table. Could you explain to me why it does not let me add another table when NOT EXISTS is in there? Thanks for the reply also.

Okay that isn't making sense. What was the syntax you tried (and is now failing)?
Was This Post Helpful? 0
  • +
  • -

#5 C# Novice  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 26-September 13

Re: Help! Access SQL coding

Posted 26 September 2013 - 06:27 PM

Ok nevermind..sorry!! lol when I run this code:

SELECT DISTINCT Last_name, First_name, Gender, Age, Admit_date, Illness_desc
FROM Patient, Illness
WHERE NOT EXISTS
(SELECT *
FROM Prescription
WHERE Patient.Patient_no = Prescription.Patient_no);


The query does run, however, I get multiple results, as in I get all of the Illnesses for the only 3 patients. However, the patients do not have all of the Illnesses? I need to get rid of this redundancy and I thought that 'DISTINCT' would get rid of this?
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9195
  • View blog
  • Posts: 34,517
  • Joined: 12-June 08

Re: Help! Access SQL coding

Posted 26 September 2013 - 06:28 PM

That would be a place to use a left join.
Was This Post Helpful? 0
  • +
  • -

#7 C# Novice  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 26-September 13

Re: Help! Access SQL coding

Posted 26 September 2013 - 06:39 PM

Sorry again and I really appreciate the apply. When I googled Left Join it showed me how to do it and here is the new
code
SELECT DISTINCT Last_name, First_name, Gender, Age, Admit_date, Illness_desc
FROM Patient
LEFT JOIN Illness
ON Patient.Patient_no = Illness.Illness_desc
WHERE NOT EXISTS
(SELECT *
FROM Prescription
WHERE Patient.Patient_no = Prescription.Patient_no);


'Type mismatch in expression' pops up. Any clue?
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9195
  • View blog
  • Posts: 34,517
  • Joined: 12-June 08

Re: Help! Access SQL coding

Posted 26 September 2013 - 06:43 PM

you need to join on like columns.. that's why keys are great. Matching a patient number to an illness varchar doesn't make sense, right?

You would need ot match the illness key on the illness table to the illness key on the patient table.. or where ever you have that.
Was This Post Helpful? 0
  • +
  • -

#9 C# Novice  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 26-September 13

Re: Help! Access SQL coding

Posted 26 September 2013 - 06:55 PM

That makes sense..so used since I know more about joins than left joins I used an INNER JOIN as
SELECT DISTINCT Last_name, First_name, Gender, Age, Admit_date, Illness_desc
FROM (Patient, Diagnosis, Illness
INNER JOIN Diagnosis
ON Diagnosis.Patient_no = Patient.Patinet_no)
INNER JOIN Illness ON Diagnosis.Illness_code = Illness.Illness_code))
WHERE NOT EXISTS
(SELECT *
FROM Prescription
WHERE Patient.Patient_no = Prescription.Patient_no);


The Illness_desc table is only located in the Illness table and no where else. Its giving me an error from the 'FROM'
clause? Am i not joining like columns in this code as well?
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9195
  • View blog
  • Posts: 34,517
  • Joined: 12-June 08

Re: Help! Access SQL coding

Posted 26 September 2013 - 07:01 PM

2	FROM (Patient, Diagnosis, Illness

Stop adding multiple tables.. seriously.. stop that. No mas.

3	INNER JOIN Diagnosis

Inner join? Any reason why?

think about it as chaining together like data to get more.


from patient a
join diagnosis b  
ON a.patientkey = b.patientkey  '-- you are matching keys that exist in one table and exist in another...
                                 '-- we also want to EXCLUDE not matching items so we just a regular ol' join. 
left join illness C 
on b.illnesskey = c.illnesskey  '-- we do not want to exclude information (since we are only in it for the name...) so we use a left join.. it doesn't drop off not matching columns.


This means we can use the illnessname in the select statement as well as the patient name from a.
Was This Post Helpful? 0
  • +
  • -

#11 C# Novice  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 26-September 13

Re: Help! Access SQL coding

Posted 26 September 2013 - 07:13 PM

Quote

Stop adding multiple tables.. seriously.. stop that. No mas.

LOL im sorry.
I understand what your saying so I came up with this..

HOWEVER, i dont really care too much if its right or wrong, I just need some clarification.

I only have the illness_desc in one table, so how can I match this with another table if its only found in one?

And again I 'am really new at this and am willing to learn and understand this.

SELECT DISTINCT Last_name, First_name, Gender, Age, Admit_date, Illness_desc
FROM (Patient P
JOIN Diagnosis D
ON P.Patient_no = D.Patient_no
LEFT JOIN Illness I ON D.Illness_code = I.Illness_code
WHERE NOT EXISTS
(SELECT *
FROM Prescription
WHERE Patient.Patient_no = Prescription.Patient_no);

Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9195
  • View blog
  • Posts: 34,517
  • Joined: 12-June 08

Re: Help! Access SQL coding

Posted 26 September 2013 - 08:21 PM

Quote

I only have the illness_desc in one table, so how can I match this with another table if its only found in one?

What?
Was This Post Helpful? 0
  • +
  • -

#13 C# Novice  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 26-September 13

Re: Help! Access SQL coding

Posted 27 September 2013 - 05:24 AM

View Postmodi123_1, on 26 September 2013 - 08:21 PM, said:

Quote

I only have the illness_desc in one table, so how can I match this with another table if its only found in one?

What?


Yeah, sorry about that and I completed the SQL and its all done. Thanks again for your help! Sorry, for the confusion lol.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1