kosiew commented on issue #14799:
URL: https://github.com/apache/datafusion/issues/14799#issuecomment-2673788381
The error "Schema error: Ambiguous reference to unqualified field id" occurs
because multiple tables in your query contain a column named id, and you're
using USING (id), which requires id to be unambiguous in all participating
tables.
In your sql, second_agg, third_agg, and fourth_random_table all originate
from right_table, id becomes ambiguous when SQL tries to determine which one to
use in further joins.
Here's how you can resolve the ambiquity:
```sql
WITH first_agg AS (
SELECT id, COUNT(*) AS count_first FROM left_table GROUP BY id
),
second_agg AS (
SELECT id, COUNT(*) AS count_second FROM right_table GROUP BY id
),
third_agg AS (
SELECT id, COUNT(*) AS count_third FROM right_table GROUP BY id
),
fourth_random_table AS (
SELECT id, category FROM right_table GROUP BY id, category
)
SELECT
fa.count_first,
frt.category,
sa.count_second,
ta.count_third
FROM first_agg fa
LEFT JOIN fourth_random_table frt ON fa.id = frt.id
LEFT JOIN second_agg sa ON fa.id = sa.id
LEFT JOIN third_agg ta ON fa.id = ta.id;
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For additional commands, e-mail: github-h...@datafusion.apache.org
