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 business rates are related to each cmi cross reference? List cross reference id, master customer id and the n
assistant
SELECT T2.cmi_cross_ref_id , T2.master_customer_id , count(*) FROM Business_Rates AS T1 JOIN CMI_Cross_References AS T2 ON T1.cmi_cross_ref_id = T2.cmi_cross_ref_id GROUP BY T2.cmi_cross_ref_id
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
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
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
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
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 ;