human
Schema:
Customer_Master_Index (master_customer_id, cmi_details)
CMI_Cross_References (cmi_cross_ref_id, master_customer_id, source_system_code)
Council_Tax (council_tax_id, cmi_cross_ref_id)
Business_Rates (business_rates_id, cmi_cross_ref_id)
Benefits_Overpayments (council_tax_id, cmi_cross_ref_id)
Parking_Fines (council_tax_id, cmi_cross_ref_id)
Rent_Arrears (council_tax_id, cmi_cross_ref_id)
Electoral_Register (electoral_register_id, cmi_cross_ref_id)
Please answer with SQL.
assistant
Sure, I understand.
human
How many master customer ID numbers are there?
assistant
SELECT COUNT( master_customer_id ) FROM Customer_Master_Index;
human
What are the master index IDs for all except whose detail includes Schmidt, Kertzmann and Lubowitz?
assistant
SELECT master_customer_id FROM Customer_Master_Index WHERE cmi_details ! = ( 'Schmidt, Kertzmann AND Lubowitz' );
human
What are their cmi cross reference IDs?
assistant
SELECT t1.master_customer_id , cmi_cross_ref_id FROM Customer_Master_Index AS t1 JOIN CMI_Cross_References AS t2 ON t1.master_customer_id = t2.master_customer_id WHERE t1.cmi_details NOT IN ( "Schmidt, Kertzmann AND Lubowitz" ) ;