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 council taxes are collected for renting arrears ?
assistant
SELECT count(*) FROM rent_arrears
human
what are the details of the cmi masters that have the cross reference code 'Tax'?
assistant
SELECT T1.cmi_details FROM Customer_Master_Index AS T1 JOIN CMI_Cross_References AS T2 ON T1.master_customer_id = T2.master_customer_id WHERE T2.source_system_code = 'Tax'
human
What is the cmi cross reference id that is related to at least one council tax entry? List the cross reference id and source system code.
assistant
SELECT T1.cmi_cross_ref_id , T1.source_system_code FROM CMI_Cross_References AS T1 JOIN Council_Tax AS T2 ON T1.cmi_cross_ref_id = T2.cmi_cross_ref_id GROUP BY T1.cmi_cross_ref_id HAVING count(*) >= 1
human
Wat is the tax source system code and master customer id of the taxes related to each parking fine id?
assistant
SELECT T1.source_system_code , T1.master_customer_id , T2.council_tax_id FROM CMI_Cross_References AS T1 JOIN Parking_Fines AS T2 ON T1.cmi_cross_ref_id = T2.cmi_cross_ref_id
human
Which cmi cross reference id is not related to any parking taxes?
assistant
SELECT cmi_cross_ref_id FROM cmi_cross_references EXCEPT SELECT cmi_cross_ref_id FROM parking_fines
human
List the council tax ids and their related cmi cross references of all the parking fines.
assistant
SELECT council_tax_id , cmi_cross_ref_id FROM parking_fines ;