Author |
Topic |
Mayank79
Starting Member
8 Posts |
Posted - 2012-02-15 : 05:17:02
|
Guys these are the table avaliable Patient (PatientID, FamilyName, GivenName, Address, Suburb, State, PostCode)Doctor (ProviderNo, Name)Item (ItemNo, Description, Fee)Account (AccountNo, ProviderNo, PatientID, Date)AccountLine (AccountNo, ItemNo)what i need to get is Name of patients who have been treated by Dr Brian or who have had anExtended Consultationwhich would be where NAME='Dr Brain' AND HAVING COUNT(DTOOHEY.AccountLine.AccountNo) >1I have been trying to get this for like 2 hours now and i have failed miserabally please help My CodeSELECT GivenName, NameFROM DTOOHEY.Patient, DTOOHEY.doctor, DTOOHEY.Account, DTOOHEY.AccountLineWHERE DTOOHEY.Patient.PatientID =DTOOHEY.Account.PatientID AND DTOOHEY.Doctor.ProviderNo =DTOOHEY.Account.ProviderNoAND DTOOHEY.AccountLine.AccountNo =DTOOHEY.Account.AccountNoINTERSECTselect AccountNo, count(AccountNo) from DTOOHEY.AccountLine GROUP BY AccountNo ;Cheers |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-15 : 05:27:30
|
How do you define an 'extended consultation'?p.s. Please join your tables with JOIN statements not in the where clause.--Gail ShawSQL Server MVP |
|
|
Mayank79
Starting Member
8 Posts |
Posted - 2012-02-15 : 05:37:37
|
Extended consultation is when their account number is appeared more than once in AccountLine.I dont know how to a join with so many tables, could you please help me that.Cheers |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-15 : 05:49:48
|
FROM <first table> INNER JOIN <second table> ON <join condition>INNER JOIN <third table> ON <Join condition>Simple as that.--Gail ShawSQL Server MVP |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-15 : 05:51:28
|
Your table structure doesn't make sense...If a particular person visits three times over the course of a month, do they have one account number or three?--Gail ShawSQL Server MVP |
|
|
Mayank79
Starting Member
8 Posts |
Posted - 2012-02-15 : 05:54:00
|
This is my college assignment.I really cant help it, most of the stuff they gave us to do does not make sense. But I have to give them the queries anyway. |
|
|
Mayank79
Starting Member
8 Posts |
Posted - 2012-02-15 : 05:59:55
|
My Code right now!!_______________________________SELECT GivenName FROM DTOOHEY.Patient, DTOOHEY.doctor, DTOOHEY.Account, DTOOHEY.AccountLineWHERE DTOOHEY.Patient.PatientID =DTOOHEY.Account.PatientID AND DTOOHEY.Doctor.ProviderNo =DTOOHEY.Account.ProviderNo AND DTOOHEY.AccountLine.AccountNo =DTOOHEY.Account.AccountNoINTERSECTSELECT DTOOHEY.AccountLine.AccountNo, count(AccountNo) FROM DTOOHEY.AccountLine HAVING COUNT (AccountNo)>1 GROUP BY AccountNo ; |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-15 : 07:13:34
|
Joins should be in the FROM clause not the where clause. I showed you how.--Gail ShawSQL Server MVP |
|
|
Mayank79
Starting Member
8 Posts |
Posted - 2012-02-15 : 07:31:44
|
Like This you mean ???SELECT DISTINCT GivenNameFROM DTOOHEY.Patient INNER JOIN DTOOHEY.Account ON DTOOHEY.Patient.PatientID =DTOOHEY.Account.PatientID INNER JOIN DTOOHEY.doctor ON DTOOHEY.Doctor.ProviderNo =DTOOHEY.Account.ProviderNo INNER JOIN DTOOHEY.AccountLine ON DTOOHEY.AccountLine.AccountNo =DTOOHEY.Account.AccountNoWHERE DTOOHEY.Doctor.Name='Dr Brian' OR (HAVING COUNT (AccountNo)>1 GROUP BY GivenName ; ); If you can correct the final statement ... it would be awsome |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-15 : 09:33:17
|
If you can tell me what it's not doing, that would be useful... Though that OR is syntatically incorrect. Having is a separate clause, as is group by. You probably need a subquery (or an INTERSECT like you had) to get the accounts with more than one line.Seeing as you haven't posted table definitions or sample data I can't test it myself.--Gail ShawSQL Server MVP |
|
|
X002548
Not Just a Number
15586 Posts |
|
Mayank79
Starting Member
8 Posts |
Posted - 2012-02-15 : 10:03:13
|
I just need to combine and give me results which have either DR brain as a doc or the COUNT (AccountNo)>1.ITs an either or situation.I am so sorry i was not sure what all i had to post here. Here are the table descriptions__________________________________________DESCRIBE DTOOHEY.ACCOUNTLINEName Null Type --------- -------- ----------- ACCOUNTNO NOT NULL NUMBER ITEMNO NOT NULL VARCHAR2(5) DESCRIBE DTOOHEY.patientName Null Type ---------- -------- ----------- PATIENTID NOT NULL NUMBER FAMILYNAME VARCHAR2(15) GIVENNAME VARCHAR2(20) ADDRESS VARCHAR2(35) SUBURB VARCHAR2(20) STATE VARCHAR2(5) POSTCODE VARCHAR2(4) DESCRIBE DTOOHEY.accountName Null Type ------------- -------- ----------- ACCOUNTNO NOT NULL NUMBER PROVIDERNO NOT NULL VARCHAR2(8) PATIENTID NOT NULL NUMBER TREATMENTDATE DATE DESCRIBE DTOOHEY.DOCTORName Null Type ---------- -------- ------------ PROVIDERNO NOT NULL VARCHAR2(8) NAME NOT NULL VARCHAR2(35) DESCRIBE DTOOHEY.ITEMName Null Type ----------- -------- ------------ ITEMNO NOT NULL VARCHAR2(5) DESCRIPTION NOT NULL VARCHAR2(30) FEE NUMBER |
|
|
X002548
Not Just a Number
15586 Posts |
|
Mayank79
Starting Member
8 Posts |
|
X002548
Not Just a Number
15586 Posts |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-15 : 11:38:33
|
You're using Oracle, not SQL Server, which makes everything I've said useless and all the time spent a complete waste.--Gail ShawSQL Server MVP |
|
|
Mayank79
Starting Member
8 Posts |
Posted - 2012-02-15 : 12:25:36
|
This is probably one of the worst forums i have been on !!You useless fucks!! |
|
|
X002548
Not Just a Number
15586 Posts |
|
X002548
Not Just a Number
15586 Posts |
|
|