Question about displaying duplicate query results

[RequiemStudio]

Hi everyone - new CF developer here.

Question: I am running a query on a database that is returning regions of a state, (i.e. Beverly Hills, CA or Irvine, CA). The query is nested in an output loop that feeds variables to a list for navigation purposes. My problem is that the query is returning duplicate regions because of the way the DB is setup and I can't change the DB.

The DB is set up like this:

Item Region State

I queryed all to pull the info and then queryed that query for the regions as compared to states. Because there are multiple items in one state I'm getting duplicate infomation returned.

I end up with:


Arizona
- Pheonix
New York
- New York
- New York
- New York

When New York should only appear once. How would I query to return just one region if there are duplicates in the DB?

Hope this is making some sense....

Thanks for any help. :)

[websiteideas]

Try SELECT UNIQUE or DISTINCT.

[RequiemStudio]

Thank you - I was unware of those SQL statements. I'm relatively new to dealing with Databases especially ones I didn't create.

[ccoonen]

Anytime you are required to use 'Distinct' it is usually a tell-tale sign that your query is wrong. Make sure your joins are correct - like you might be hitting it with a Left Outer Join instead of an Inner join.