Audit intermediate Works with: Any AI

SQL Join Fan-Out and Duplicate Row Audit

Checklist Has example

• 🗄️ SQL

Find join logic that multiplies rows and inflates totals, with a test query plan and corrected pattern for each risky join.

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.

I suspect one of my SQL queries is counting rows twice because of how tables are joined. Database type: [DATABASE_TYPE]. I will paste the query: [QUERY]. I will also paste the table definitions with their keys: [TABLE_DEFINITIONS]. A sample of the result and the number I expected: [OBSERVED_VS_EXPECTED].

Please review it as a careful analyst. Report: 1) A join-by-join table with the columns Join, Relationship (one to one, one to many, many to many, unknown), Could it multiply rows? and Evidence from the keys. 2) The most likely cause of the wrong number, explained in plain English in under 120 words. 3) A set of small diagnostic queries I can run that count rows before and after each join, and check for duplicate keys, so I can prove the cause myself. 4) A corrected version using a technique such as aggregating before joining, or joining to a de-duplicated subquery, with a comment on every change. 5) A note on which sums or counts need DISTINCT and why that is sometimes only a patch.

Rules: I want teaching, not magic. If the table definitions are missing a key, say what you are assuming and mark it [VERIFY]. Do not run or imagine results you cannot see; give the checks instead. Syntax varies by database, so mark any function specific to [DATABASE_TYPE] [VERIFY]. Always advise testing on a copy or read-only connection.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Likely cause (invented case): orders joins to order_items (one to many), then to payments (one to many). An order with 3 items and 2 payments produces 6 rows, so SUM(order_total) counts it 6 times.

| Join | Relationship | Multiplies rows? |
| orders to order_items | one to many | Yes |
| orders to payments | one to many | Yes, combined with the first |

Diagnostic query:
SELECT order_id, COUNT(*) AS row_count FROM joined_result GROUP BY order_id HAVING COUNT(*) > 1;

Fix: aggregate payments to one row per order in a subquery before joining, then sum order_total once. Check on a copy; syntax may differ by database [VERIFY].