SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Can you please fix the code?
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mayank79
Starting Member

8 Posts

Posted - 02/15/2012 :  05:17:02  Show Profile
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 02/15/2012 :  05:27:30  Show Profile  Visit GilaMonster's Homepage
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 - 02/15/2012 :  05:37:37  Show Profile
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 02/15/2012 :  05:49:48  Show Profile  Visit GilaMonster's Homepage
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 02/15/2012 :  05:51:28  Show Profile  Visit GilaMonster's Homepage
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 - 02/15/2012 :  05:54:00  Show Profile
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 - 02/15/2012 :  05:59:55  Show Profile
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 02/15/2012 :  07:13:34  Show Profile  Visit GilaMonster's Homepage
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 - 02/15/2012 :  07:31:44  Show Profile
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 02/15/2012 :  09:33:17  Show Profile  Visit GilaMonster's Homepage
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 - 02/15/2012 :  09:55:59  Show Profile
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 - 02/15/2012 :  10:03:13  Show Profile
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 - 02/15/2012 :  10:31:12  Show Profile
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 - 02/15/2012 :  10:42:29  Show Profile
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 - 02/15/2012 :  11:12:41  Show Profile
??? 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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 02/15/2012 :  11:38:33  Show Profile  Visit GilaMonster's Homepage
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 - 02/15/2012 :  12:25:36  Show Profile
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 - 02/15/2012 :  12:26:51  Show Profile
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 - 02/15/2012 :  12:28:28  Show Profile
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
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000