Home » SQL & PL/SQL » SQL & PL/SQL » SQL syntax help
SQL syntax help [message #194559] Fri, 22 September 2006 11:18 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I'm trying to write a query that lists beds that have a patient in them AND shows what beds are empty. There are two tables involved and I can't seem to get the results I want. Can someone give me a kick start in the right direction?

The first query lists all beds with a patient

select
   p.dept,
   p.room,
   p.bed
from
   pat p
where
   p.bed_id IS NOT NULL AND
   p.building = '1'
order by
   dept,
   room,
   bed


The second query lists all beds for all buildings

SELECT
   a.DEPT,
   a.ROOM,
   a.BED
FROM
   bed a



I would expect this query to return all empty beds but I'm getting a listing of all beds from the masterlist. I'm stuck at this point.

select
   a.dept,
   a.room,
   a.bed
from
   bed a
where
   a.building = 'r' AND
   a.inactive_dt IS NULL AND
   a.bed_id <> '.' AND
   a.dept||'-'||a.room||'-'||a.room not in
(select ip.pidrb
  from
   (select
   p.dept||'-'||p.room||'-'|| p.bed as pidrb
   from
      pat p
   where
      p.building = '1' AND
      p.bed IS NOT NULL) ip)
ORDER BY
   DEPT,
   ROOM,
   BED


Thanks for looking
Stan

[Updated on: Sat, 23 September 2006 08:13]

Report message to a moderator

Re: SQL syntax help [message #194585 is a reply to message #194559] Fri, 22 September 2006 16:57 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps something like this?

As you already have two queryes, combine them like this:
SELECT beds WITH a patient
UNION
(
SELECT ALL beds
MINUS
SELECT beds WITH a patients
)
However, why wouldn't you just change the WHERE clause of a query which lists taken beds (i.e. change "is NOT null" to "is null")?
SELECT beds WITH a patient
UNION
SELECT beds WITH a patient WHERE p_bed_id IS NULL
Re: SQL syntax help [message #194596 is a reply to message #194559] Fri, 22 September 2006 19:51 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thanks for getting me to the next step...now the problem I'm having is I need to include the patients name with the bed number. The table structures are like the following:

table masterlist lists the following:

building dept room bed

1 A A 1
1 A A 2
1 A B 1
1 A B 2
2 A A 1
2 A A 2
2 A B 1
2 A B 2

table pat lists the following:

building dept room bed patient

1 A A 1 john
1 A B 1 scott
1 A B 2 jan
2 A A 1 jean

I would like the results to look like this:

building dept room bed patient

1 A A 1 john
1 A A 2 empty bed
1 A B 1 scott
1 A B 2 jan
2 A A 1 jean
2 A A 2 empty bed
2 A B 1 empty bed
2 A B 2 empty bed

select
   dept||'-'||room||'-'||bed
from
   pat
where
   bed_id is not null and 
   building = '1'   

union


(SELECT
   dept||'-'||room||'-'||bed
FROM
   bed
WHERE
   building = 1  
MINUS
SELECT
   dept||'-'||room||'-'||bed
FROM
   pat
WHERE
   bed IS NOT NULL AND 
   building = 1)


What I'm getting is:

building dept room bed

1 A A 1
1 A A 2
1 A B 1
1 A B 2
2 A A 1
2 A A 2
2 A B 1
2 A B 2

but I need the patient listed next to the bed they're in so I can see the empty bed.

Thanks,

Stan
Re: SQL syntax help [message #194625 is a reply to message #194559] Sat, 23 September 2006 01:35 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

What about ???



select
   dept||'-'||room||'-'||bed||'-'||patient
from
   pat
where
   bed_id is not null and 
   building = '1'   
union
(SELECT
   dept||'-'||room||'-'||bed||'-'||'Empty-bed'
FROM
   bed
WHERE
   building = 1  
MINUS
SELECT
   dept||'-'||room||'-'||bed||'-'||'Empty-bed'
FROM
   pat
WHERE
   bed IS NOT NULL AND 
   building = 1)


Thumbs Up
Rajuvan.

[Updated on: Sat, 23 September 2006 01:36]

Report message to a moderator

Re: SQL syntax help [message #194645 is a reply to message #194559] Sat, 23 September 2006 08:11 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
That did it. But now I've run into another problem. I'm needing to pull about ten fields from the pat table and there's only about five fields in the bed table. Is there a way to get this done with different number of fields? I don't think this can be done with a union.


Thanks,
Stan

[Updated on: Sat, 23 September 2006 18:11]

Report message to a moderator

Re: SQL syntax help [message #194747 is a reply to message #194559] Mon, 25 September 2006 01:51 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Even this can be done by UNION.

select
   dept||'-'||room||'-'||bed||'-'||patient,Field6,Field7,Field8,Field9,Field10
from
   pat
where
   bed_id is not null and 
   building = '1'   
union
(SELECT
   dept||'-'||room||'-'||bed||'-'||'Empty-bed',NULL,NULL,NULL,NULL,NULL
FROM
   bed
WHERE
   building = 1  
MINUS
SELECT
   dept||'-'||room||'-'||bed||'-'||'Empty-bed',NULL,NULL,NULL,NULL,NULL
FROM
   pat
WHERE
   bed IS NOT NULL AND 
   building = 1)


Thumbs Up
Rajuvan.
Re: SQL syntax help [message #194804 is a reply to message #194747] Mon, 25 September 2006 07:10 Go to previous message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thank you so much. That did it.

Stan
Previous Topic: Create table invalid syntax
Next Topic: Renaming Table
Goto Forum:
  


Current Time: Thu Dec 08 18:32:45 CST 2016

Total time taken to generate the page: 0.11121 seconds