Why AI Keeps Generating Bad SQL Even When The Schema Is Correct

One thing I've noticed while testing Text-to-SQL systems:
The schema is often fine.
The model is often fine.
The SQL is often syntactically correct.
The answer is still wrong.
Why?
Because SQL generation isn't the hard part.
Join selection is.
Imagine a warehouse containing:
orders
customers
subscriptions
invoices
accounts
A model may know all five tables exist.
The challenge is deciding:
Which relationship path should be used?
That's where many systems fail.
Most Text-to-SQL architectures focus on:
Schema → Prompt → SQL
But production environments usually require:
Schema
↓
Relationship Discovery
↓
Trusted Join Path
↓
Prompt
↓
SQL
Without relationship context, the model is forced to guess.
And enterprise analytics is a terrible place for guessing.