Which assets have 2 parts and have less than 2 fault logs? List the asset id and detail.
CREATE TABLE Assets (asset_id VARCHAR, asset_details VARCHAR); CREATE TABLE Asset_Parts (asset_id VARCHAR); CREATE TABLE Fault_Log (asset_id VARCHAR)
SELECT T1.asset_id, T1.asset_details FROM Assets AS T1 JOIN Asset_Parts AS T2 ON T1.asset_id = T2.asset_id GROUP BY T1.asset_id HAVING COUNT(*) = 2 INTERSECT SELECT T1.asset_id, T1.asset_details FROM Assets AS T1 JOIN Fault_Log AS T2 ON T1.asset_id = T2.asset_id GROUP BY T1.asset_id HAVING COUNT(*) < 2
<s>[INST] Write mssql query to answer the following question given the database schema. Schema: CREATE TABLE Assets (asset_id VARCHAR, asset_details VARCHAR); CREATE TABLE Asset_Parts (asset_id VARCHAR); CREATE TABLE Fault_Log (asset_id VARCHAR) Question: Which assets have 2 parts and have less than 2 fault logs? List the asset id and detail. [/INST] SELECT T1.asset_id, T1.asset_details FROM Assets AS T1 JOIN Asset_Parts AS T2 ON T1.asset_id = T2.asset_id GROUP BY T1.asset_id HAVING COUNT(*) = 2 INTERSECT SELECT T1.asset_id, T1.asset_details FROM Assets AS T1 JOIN Fault_Log AS T2 ON T1.asset_id = T2.asset_id GROUP BY T1.asset_id HAVING COUNT(*) < 2 </s>