SQL query - Sort by the Primary Ship to Location

Hi Experts,

In my database, a customer can have multiple ship to locations but only 1 primary ship to location.

In this example, I am pulling the customer ship to location from 1 table and the primary ship to location from another table. I will be loading this information into a drop down box and would like the primary ship to location to the default value. If no primary ship to location is defined, it will load the locations by alphabetical order.

The ARCUS.PRIMSHIPTO is the primary ship to location field.

SELECT     ARCSP.IDCUST, ARCSP.IDCUSTSHPT, ARCSP.NAMELOCN, ARCSP.SHPVIACODE, ARCUS.PRIMSHIPTO
FROM         ARCSP INNER JOIN
                      ARCUS ON ARCSP.IDCUST = ARCUS.IDCUST
WHERE     (ARCSP.IDCUST = @IDCUST)

Open in new window

Microsoft SQL ServerMySQL ServerSQL

Last Comment

jnsimex

8/22/2022 - Mon

Jim Horn

3/17/2016

Does 'load the locations by alphabetical order.' mean that multiple rows will be returned if there are multiple locaitons?

Kinda hard to translate your English requirements into ARCSP and ARCSP, so a sample data set would help.

For starters..

SELECT   
   ARCSP.IDCUST, 
   ARCSP.IDCUSTSHPT, 
   ARCSP.NAMELOCN, 
   ARCSP.SHPVIACODE, 
   CASE WHEN ARCUS.PRIMSHIPTO IS NOT NULL THEN ARCUS.PRIMSHIPTO ELSE ??? END as PRIMSHIPTO
FROM ARCSP 
   JOIN ARCUS ON ARCSP.IDCUST = ARCUS.IDCUST
WHERE ARCSP.IDCUST = @IDCUST

Open in new window

jnsimex

3/17/2016

ASKER

Hi Jim,

Yes, multiple rows will be returned if there are multiple locations.

I have attached a screen shot of the current query results. The customer "FED005" has 4 ship to location (IDCUSTSHPT) and the primary ship to location is "HOBSON". In the dropdown list, I would like to have "HOBSON" as the default value in the drop down list text box but they can select another location by clicking it. If there is no value in PRIMSHIPTO, then all locations are loaded alphabetically by IDCUSTSHPT.

I hope this makes more sense now.