I have two MySQL tables I'm working with. The first table (named inventory) contains inventory records of all company computers that we have. The primary key is "InvNum."
The second table (named maintenance) contains records of all maintenance that was done to any computer inventory item. It uses its own "InvNum" as a foreign key, linking it to the inventory table. You cannot enter a maintenance record for a non-existent InvNum or you will get an error. (A little referential integrity there.)
The inventory table also contains a boolean field named "is_active." If a computer is taken out of service it is marked with a zero (indicates inactive). We never delete any records because that inventory item may well be returned to active status later on (e.g. we replaced the hard drive).
The query syntax for retrieving any maintenance record is simple:
$query = "SELECT * FROM maintenance WHERE InvNum = 2001;
But I would also like to get the associated active status (in the inventory table) for this item at the same time. I've tried doing a subquery like this:
$query = SELECT * FROM maintenance WHERE InvNum = 2001 AND (SELECT is_active FROM inventory WHERE InvNum = 2001);
The query works, but it does not return the status of the is_active field value, only a null. IOW, it returns all maintenance records for inventory item #2001, but not the active status for this item. 😕
