Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Can you please fix the code?

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 an
Extended Consultation


which would be where NAME='Dr Brain' AND HAVING COUNT(DTOOHEY.AccountLine.AccountNo) >1

I have been trying to get this for like 2 hours now and i have failed miserabally please help


My Code

SELECT GivenName, Name
FROM DTOOHEY.Patient, DTOOHEY.doctor, DTOOHEY.Account, DTOOHEY.AccountLine
WHERE
DTOOHEY.Patient.PatientID =DTOOHEY.Account.PatientID
AND
DTOOHEY.Doctor.ProviderNo =DTOOHEY.Account.ProviderNo
AND
DTOOHEY.AccountLine.AccountNo =DTOOHEY.Account.AccountNo




INTERSECT

select 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 Shaw
SQL Server MVP
Go to Top of Page

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
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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.
Go to Top of Page

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.AccountLine
WHERE
DTOOHEY.Patient.PatientID =DTOOHEY.Account.PatientID
AND
DTOOHEY.Doctor.ProviderNo =DTOOHEY.Account.ProviderNo
AND
DTOOHEY.AccountLine.AccountNo =DTOOHEY.Account.AccountNo
INTERSECT

SELECT DTOOHEY.AccountLine.AccountNo, count(AccountNo)
FROM DTOOHEY.AccountLine
HAVING COUNT (AccountNo)>1 GROUP BY AccountNo ;
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

Mayank79
Starting Member

8 Posts

Posted - 2012-02-15 : 07:31:44
Like This you mean ???



SELECT DISTINCT GivenName
FROM 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.AccountNo
WHERE
DTOOHEY.Doctor.Name='Dr Brian'
OR
(HAVING COUNT (AccountNo)>1 GROUP BY GivenName ; );




If you can correct the final statement ... it would be awsome
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 09:55:59
Go ask the teacher assistant

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

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.ACCOUNTLINE
Name Null Type
--------- -------- -----------
ACCOUNTNO NOT NULL NUMBER
ITEMNO NOT NULL VARCHAR2(5)

DESCRIBE DTOOHEY.patient
Name 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.account
Name Null Type
------------- -------- -----------
ACCOUNTNO NOT NULL NUMBER
PROVIDERNO NOT NULL VARCHAR2(8)
PATIENTID NOT NULL NUMBER
TREATMENTDATE DATE

DESCRIBE DTOOHEY.DOCTOR
Name Null Type
---------- -------- ------------
PROVIDERNO NOT NULL VARCHAR2(8)
NAME NOT NULL VARCHAR2(35)

DESCRIBE DTOOHEY.ITEM
Name Null Type
----------- -------- ------------
ITEMNO NOT NULL VARCHAR2(5)
DESCRIPTION NOT NULL VARCHAR2(30)
FEE NUMBER


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 10:31:12
As this is a SQL Server site, you might want to go ask here

http://www.dbforums.com/oracle/



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

Mayank79
Starting Member

8 Posts

Posted - 2012-02-15 : 10:42:29
Brett,

I dont even have a teacher for this course.


quote:
Originally posted by X002548

Go ask the teacher assistant

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/




Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 11:12:41
??? How can it be a College Assignment?

And Again, you are using Oracle or SQL Server?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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!!
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 12:26:51
I would imagine that ANYWHERE you go will be the same

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 12:28:28
Did you see the SQL Server MVP Title in the Sig?

Good Luck in your "Career"

Repeat after me

"Do you want Fries with that?"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page
   

- Advertisement -