Best practice for unique reference list, no duplicates

[Hyman_van_Zyl]

Hey guys,
I have come around this scenario several times where I need to create a reference list using select based on the reference key of another table but then I end up with a referenced list full of duplicates. It happens with a many to many relation. Each key in the many to many table is unique so you cant use TRUE but the item repeats. If you create a total per unique item each similar item will display the same total.
What is the best practice to list those unique items with totals in one of the relating tables?
Thanks
Hyman

[Steve]

Hyman_van_Zyl:

Each key in the many to many table is unique so you cant use TRUE but the item repeats.

What does this mean?

Hyman_van_Zyl:

If you create a total per unique item each similar item will display the same total.

And this?

[Hyman_van_Zyl]

Dear Steve,
Thanks for your reply. Sorry for my late reply. And I see now my question wasnt clear. Although your advice was accurate.

I mean about TRUE that if I create a SELECT for the key column of a many by many with a condition on one of the related tables in the many by many table then a list of duplicates of that related table will display in the SELECT list even if you use TRUE. I mention it to try demonstrate my problem. A bit obvious though.

So if you created a sum select total with condition on one of the related tables in the many to many, the above list will therefore have the same total for same items in the list.

I solved it by adding a column to the related table I want to base the total on, give it a count select initial value and then add a max condition to the select condition. This then creates a list of the many to many table only selecting the highest initial count for same items creating a list of unique values.

Thanks lots for your help.

Hyman

[Steve]

Although not supported behavior, subtracting one list from another has the side-effect of removing duplicates. So this:

SELECT(..., ..., TRUE)

is equivalent to this:

SELECT(..., ..., FALSE) - LIST()

Or, if you’re combining several lists that overlap:

LIST(...) + SELECT(...) + { ... } - LIST()

would merge the lists and remove duplicates.

Again, this is a coincidental side-effect and unsupported behavior. The behavior may change in the future without notice.