Re: How to list parent's table's attribute via sql without function or trigger?

This is the standard query for obtaining duplicate entries in a table. The first three lines of the query count the number of entries for each unique combination of id1 and id2. The last line filters the list to show only duplicates. If the query does not return any rows, you do not have duplicate id1 and id2 combinations.

Are your foreign keys really declared as table constraints? You can only declare foreign keys that reference columns that are in a primary or unique constraint. If so, t2.id, t3.category and t4.id2 should not have duplicates. And since you verified that the t1.id1 and t1.id2 columns don't have duplicates, I don't see the source of the duplicates.

Now, if you were just using "fk" to loosely describe the relationships between the tables, then any of the other tables could be the culprit. I taught you how to find duplicates, so formulate similar queries for t2, t3, and t4 to find the bad boy.

Alternately, you could incrementally build your query to find the source of the duplicates. Start with t1. Check for dups. Add t2. Check for dups. And so on.

--
Later,
BEDick