query help

[will35010]

I'm trying to create a select query that returns a list of rooms that aren't being used. I need help creating a query that shows me the rooms.room that aren't being used in visit_data.room. When a room is being occupied, my script puts the value from rooms.room into visit_data.room.

 

Any help would be greatly appreciated!!!

 

 

--
-- Table structure for table `rooms`
--

CREATE TABLE IF NOT EXISTS `rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `areaid` varchar(50) NOT NULL,
  `room` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

INSERT INTO `rooms` (`id`, `areaid`, `room`) VALUES
(1, 'ED', 'TA'),
(7, 'ED', 'TB'),
(9, 'ED', 'T2');

--
-- Table structure for table `visit_data`
--

CREATE TABLE IF NOT EXISTS `visit_data` (
  `visitid` int(11) NOT NULL AUTO_INCREMENT,
  `patientid` varchar(45) NOT NULL,
  `priority` varchar(100) NOT NULL,
  `alert1` varchar(100) DEFAULT '1',
  `alert2` varchar(100) DEFAULT '1',
  `alert3` varchar(100) DEFAULT '1',
  `cc` varchar(50) NOT NULL,
  `walkout` varchar(3) DEFAULT NULL,
  `areaid` varchar(45) NOT NULL,
  `room` varchar(45) DEFAULT NULL,
  `current_status` varchar(45) DEFAULT NULL,
  `doctor` varchar(50) DEFAULT NULL,
  `nurse` varchar(50) DEFAULT NULL,
  `reg_time` varchar(12) DEFAULT NULL,
  `discharged` varchar(45) DEFAULT NULL,
  `discharged_time` varchar(12) DEFAULT NULL,
  `disposition` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`visitid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

[Maq]

How do you know when a room is occupied?  Also, what is your FK that relates 'visit_data' and 'rooms'?

[brianlange]

You can use a left join and then in the where clause specify that the value for the column in the visit_data table is null

 

 

SELECT *

FROM  `rooms`

LEFT JOIN visit_data ON rooms.room = visit_data.room

WHERE visit_data.room IS NULL   

[will35010]

Thank you for actually reading my question. Your solution worked. Thanks!!! 

 

You can use a left join and then in the where clause specify that the value for the column in the visit_data table is null

 

 

SELECT *

FROM  `rooms`

LEFT JOIN visit_data ON rooms.room = visit_data.room

WHERE visit_data.room IS NULL