sql help

Pls try ...

Some of the lcoations have the same name but different locationID

SELECT MAX(locationID) locationID , LocationName 
FROM Locations
GROUP BY LocationName 

Select allOpen in new window

Use LocationName  as Text and Value fields

LocationName  - Text to show in drop down.
LocationName  - Value field to search in DB

I would display both to the user.  In your CFQuery grab locationID and locationName then set the latter as the text of the select (drop-down) and the former as the value.  To help the user figure out which location with same name to pick, you can dynamically add text after each location like " (locationID)" either in the original SQL query or in CF when setting the text of the drop-down option.

If you the duplicate locations are a mistake, see Pawan's solution where either MIN or MAX is good depending on which of the locations is the valid one.  My response is based on business logic that I have seen some companies with ship to or sold to locations with the same location name but the location ID identifies the proper branch and as such is a unique location.

>some of the lcoations have the same name but different locationID.
>I need to create a location drop down with a unique locations
I recommend as a step before creating your dropdown scrubbing your data such that location names are unique, clarifying them when they are legitimate (Portland, ME vs. Portland, OR) and removing any duplicates / changing any necessary FK's.

Agree with Jim as well. Hey Jim by the way. *smile*
I read it originally as different locations because they were legit like your example with Portland or as I said where location ID represents something important to the business like PO12, Portland and PO13, Portland for the same company.  Would think it would be useful to see in drop-down "Portland (PO12)" then "Portland (PO13)".

For example Fairfax - Robinson High School have ID 132 and Fairfax - Robinson High School have ID 533.

On the drop down I can show one location Fairfax - Robinson High School but if I do the search for The all training Fairfax - Robinson High School I need to inlude both location.

Can i pass the name to search and then do the lookup for all location that have this name and then include a list of locations taht are matching this name

If the user knows the location IDs then you can display both as @Kevin suggests, perhaps by concatenating them together ("1234_Southport"). If they only know the name and they'll be searching by name, then they must have additional information to tell them apart so they can select the right one: the state code as @Jim suggests (1234_Southport_CA), a company name (1234_Southport_Microsoft), the name of the person who runs that location (1234_Southport_Russell), etc.

Those locations basically identical. The reason we have doubles is . We have a training locations . The training location is a part of the HUB . But different instructors are survicing different hubs and some those locations are either in or not in these HUB. The location is one to one relationship to the HUB. So one location is can only be assigned to one hub. that why we create multiple identical locations to assigned them to a multiople hubs

@erikTsomik - based on your requirement i think my only solution will help you out.

I understand it is very difficult to change the data, since you have added HUB into the locations. Please try out my solution that should help you out.

As Russell said.  Using CF functionality, you can use one trip to the database to get a detailed query then you can run a query of your query to group by location name for your drop-down list.  Then when the user selects one, you can display for them the details.

https://www.bennadel.com/blog/2211-coldfusion-query-of-queries-vs-the-group-attribute-in-cfoutput.htm

Then you should include the HUB ID or name so people can tell them apart. It would also help to include a new field, IsActive (BIT), and set that to false (zero) when that location is not in active production at that HUB.

So in that case of the HUBs, you JOIN the hub name to the detail query so when you return the multiple matches to the user they can select the hub.  If you know the hub upfront, your distinct list should take care of itself because only one location should exist then.  Therefore, you just add WHERE hubID = 'x'.

Clients do not see hubs all they see locations . HUBS are only used for internal purposes

I was just about to type CF example but see above.  It is what I was referring to with query of query.  You then can use the CSV of Ids to get locations later.  My thought was on the page, you can dynamically create the ajax (JavaScript) you need to dynamically show the locations to the user by using the cfoutput to store you a lookup but doesn't sound like you need that and just can manage having one or more ids in the form on submit.

As the clients don't know the HUBs they also must not know the Location IDs as well.
You should provide some extra information that differentiate the locations as they are seeing it. Jim's solution should be the best for you if you can add the region or state to the location name.

I disagree.  This is based on the author's requirements.   The author has stated that if the user chooses a location named "FairFax" that every ID associated with that location should be searched.   Separating the location "FairFax" into different selections will not satisfy his stated requirement.  

I do agree that a cleansing of the data to merge locations of the same name and updating all the foreign keys would be ideal, but until then...

Agree with gdemaria. Data cleaning can be handled later. User can use below to handle this situation -

SELECT LocationName 
FROM Locations
GROUP BY LocationName

Select allOpen in new window