How do I write this MySQL query to get the correct information? (Subquery, multiple subqueries)

Question

Here is the query that I am working on:

SELECT `unitid`, `name` FROM apartmentunits
WHERE aptid = (
    SELECT `aptid` FROM rentconditionsmap WHERE rentcondid = 1 AND condnum = 1
)

What I am having trouble figuring out is how to write this to add more rentcondition limiters to filter this list down more.

SELECT `aptid` FROM rentconditionsmap WHERE rentcondid = 1 AND condnum = 1

Data:

CREATE TABLE IF NOT EXISTS `rentconditionsmap` (
   `rcid` bigint(10) unsigned NOT NULL AUTO_INCREMENT, 
   `rentcondid` int(3) unsigned NOT NULL, 
   `condnum` tinyint(3) unsigned NOT NULL, 
   `aptid` bigint(10) unsigned DEFAULT NULL, 
   PRIMARY KEY (`rcid`), KEY `aptid` (`aptid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

INSERT INTO `rentconditionsmap` 
  (`rcid`, `rentcondid`, `condnum`, `aptid`) 
VALUES 
  (1, 1, 1, 1), 
  (2, 2, 1, 1), 
  (3, 3, 0, 1), 
  (4, 4, 1, 1), 
  (5, 8, 0, 1);

CREATE TABLE IF NOT EXISTS `apartmentunits` (
  `unitid` bigint(10) NOT NULL AUTO_INCREMENT, 
  `aptid` bigint(10) NOT NULL, 
  `name` varchar(6) NOT NULL, 
  `verified` tinyint(1) NOT NULL DEFAULT '0', 
  `rentcost` int(4) unsigned DEFAULT NULL, 
  `forrent` tinyint(1) NOT NULL DEFAULT '0', 
  `unittypekey` varchar(2) DEFAULT NULL, 
  `sqft` smallint(6) DEFAULT NULL, 
  PRIMARY KEY (`unitid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=121 ;

INSERT INTO `apartmentunits` 
  (`unitid`, `aptid`, `name`, `verified`, `rentcost`, `forrent`, `unittypekey`, `sqft`) 
VALUES 
  (1, 1, '3', 1, 540, 0, '2B', NULL), 
  (2, 1, '5', 1, NULL, 0, '2B', NULL), 
  (3, 1, '7', 1, NULL, 0, '2B', NULL), 
  (53, 1, '1', 1, NULL, 0, '2B', NULL), 
  (54, 1, '2', 1, NULL, 0, '2B', NULL), 
  (55, 1, '4', 1, 570, 0, '2B', NULL), 
  (56, 1, '6', 1, NULL, 0, '2B', NULL), 
  (57, 1, '8', 1, NULL, 0, '2B', NULL), 
  (58, 1, '9', 1, NULL, 0, '2B', NULL), 
  (59, 1, '10', 1, NULL, 0, '2B', NULL), 
  (60, 1, '11', 1, NULL, 0, '2B', NULL);

Answer 1

Use a JOIN (below is TSQL sintax for a join, or you can use the explicit INNER JOIN).

SELECT apartmentunits.unitid, apartmentunits.name 
FROM apartmentunits, rentconditionsmap
WHERE apartmentunits.aptid = rentconditionsmap.aptid
AND rentconditionsmap.rentcondid = 1
AND rentconditionsmap.condnum = 1
-- AND whatever else...

Answer 2

why not:

SELECT unitid, name
FROM apartmentunits a INNER JOIN rentconditionsmap r on a.aptid = r.aptid
WHERE (rentcondid = 1 and condnum = 1) OR (rentcondid = 2 and condnum = 2)

Answer 3

Using ANSI-92 join syntax:

SELECT au.unitid, 
       au.name 
  FROM APARTMENTUNITS au
  JOIN RENTCONDITIONSMAP rcm ON rcm.aptid = au.aptid
                            AND rcm.rentcondid = 1
                            AND rcm.condnum = 1

Answer 4

As Eric J said as a comment:

Try changing = to IN

SELECT `unitid`, `name` FROM apartmentunits
WHERE `aptid` IN (
    SELECT `aptid` FROM rentconditionsmap WHERE rentcondid = 1 AND condnum = 1
)