PLEASE HELP !! URGENT - SQL Server Forums

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

PLEASE HELP !! URGENT

Author

Topic

pureclass85
Starting Member

29 Posts

Posted - 2009-01-07 : 08:39:11

i am new to sql and i need a way urgently of displaying a table with data from an existing database:

There is one table called ward that has the following colums
name
ward_no
number_of_beds

The next table i have is patient that has the following colums
name
patient_no
ward_no

For each ward, give the ward number and name, the number of occupied beds and the number of empty beds on that ward.

i need the most effective way posible, thanks for anyhelp you can give

Please help!

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-01-07 : 08:42:00

we don't do homework question. they are never urgent.

anyway... look into the joins, and count() method

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 08:43:00

Something like this

SELECT		w.Ward_No,
		w.Name,
		COUNT(p.Ward_No) AS Occupied_Beds,
		MAX(w.Number_Of_Beds) - COUNT(p.Ward_No) AS Empty_Beds
FROM		Ward AS w
LEFT JOIN	Patient AS p ON p.Ward_No = w.Ward_No
GROUP BY	w.Ward_No,
		w.Name

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 08:55:12

quote:
Originally posted by pureclass85

i am new to sql and i need a way urgently of displaying a table with data from an existing database:

There is one table called ward that has the following colums
name
ward_no
number_of_beds

The next table i have is patient that has the following colums
name
patient_no
ward_no

For each ward, give the ward number and name, the number of occupied beds and the number of empty beds on that ward.

i need the most effective way posible, thanks for anyhelp you can give

Please help!

Thanks


SELECT w.ward_no as wardno,
w.name as wardname,
p.occupied as occupiedbeds
w.number_of_beds-p.occupied AS emptybeds
FROM ward w
INNER JOIN (SELECT ward_no,COUNT(patient_no) as occupied
            FROM patient
            GROUP BY ward_no) p
ON p.ward_no=w.ward_no

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 09:10:49

Visakh, will that work for wards where ALL beds are empty?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 09:40:41

[code]
SELECT w.ward_no as wardno,
w.name as wardname,
COALESCE(p.occupied,0) as occupiedbeds
w.number_of_beds-COALESCE(p.occupied,0) AS emptybeds
FROM ward w
LEFT JOIN (SELECT ward_no,COUNT(patient_no) as occupied
FROM patient
GROUP BY ward_no) p
ON p.ward_no=w.ward_no
[/code]

pureclass85
Starting Member

29 Posts

Posted - 2009-01-07 : 09:49:00

hi thank you for your reply, i cannot seam to get it to work tho.
there is no colum which contains the ocupied beds, and the occupied beds are needed seprate for each ward.

thanks again everyone

btw i am using mysql

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 09:50:44

Both mine and Visakh's second attempt will provide the data you wrote you wanted.
Why don't you give it a try?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 09:57:23

quote:
Originally posted by pureclass85

hi thank you for your reply, i cannot seam to get it to work tho.
there is no colum which contains the ocupied beds, and the occupied beds are needed seprate for each ward.

thanks again everyone

btw i am using mysql

thanks

the posted query works for sql server. if you're using mysql and posted suggestions dont work, then try your luck in mysql forums like www.dbforums.com

pureclass85
Starting Member

29 Posts

Posted - 2009-01-07 : 10:03:27

thanks anyway

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources