95lightning
Programmer
I am using microsoft SQL 7.0 via SQL Analyzer/Enterprise Manager and visual interdev.
Table Name: GT_Name
(Relevant) Columns (in order):
PK_Name, Qlfr_Name, Name, City, State, Zip, Cntry, Fk_Shipment, FK_Header
Shipment information is contained within this table. Company names are stored within the Name Column. Qlfr_Name contains the type of company....eg. Shipper, consignee, notify party...... City, State, Zip and Country are as indicated. Fk_Shipment/Fk_Header are links to other tables.
One company could and does have multiple entries within the table. Some companies have one PK_Name, others have up to 50 PK_Names. This table is filled via # of shipments.
PROBLEM:
Considering that each company entry has a unique primary key, and that specific companies can have multiple entries:
I need to select specific companies only once. If a company shows up 100 times, I only need to pull this company's PK_Name, Name, City and Country for display and passing, one time.
PK_Name is used in a followup program, in order to display the location information. Therefore it is imperative that PK_Name is acquired from the select statement. And, as before, that only one occurance of each company is captured.
You may now see the dilema that I face. Since each company occurance has it's own PK_Name, the uniqueness fades from distinguishing the company name.
The following is the select statement used that returns all of the information:
"Select PK_Name, Name, City, Cntry from GT_Name where Name <> ' ' and Name <> ',' and Name <> '.' group by Name, City, Cntry, Pk_Name Order by Mame, city, Cntry"
If I use a Distinct on name.......... I loose the PK_Name that I need to pass for program display.
If I use a Distinct on pk_name, I still get all occurances of each company.
Thanks, in advance, for your efforts!!!!!!!
John,
Table Name: GT_Name
(Relevant) Columns (in order):
PK_Name, Qlfr_Name, Name, City, State, Zip, Cntry, Fk_Shipment, FK_Header
Shipment information is contained within this table. Company names are stored within the Name Column. Qlfr_Name contains the type of company....eg. Shipper, consignee, notify party...... City, State, Zip and Country are as indicated. Fk_Shipment/Fk_Header are links to other tables.
One company could and does have multiple entries within the table. Some companies have one PK_Name, others have up to 50 PK_Names. This table is filled via # of shipments.
PROBLEM:
Considering that each company entry has a unique primary key, and that specific companies can have multiple entries:
I need to select specific companies only once. If a company shows up 100 times, I only need to pull this company's PK_Name, Name, City and Country for display and passing, one time.
PK_Name is used in a followup program, in order to display the location information. Therefore it is imperative that PK_Name is acquired from the select statement. And, as before, that only one occurance of each company is captured.
You may now see the dilema that I face. Since each company occurance has it's own PK_Name, the uniqueness fades from distinguishing the company name.
The following is the select statement used that returns all of the information:
"Select PK_Name, Name, City, Cntry from GT_Name where Name <> ' ' and Name <> ',' and Name <> '.' group by Name, City, Cntry, Pk_Name Order by Mame, city, Cntry"
If I use a Distinct on name.......... I loose the PK_Name that I need to pass for program display.
If I use a Distinct on pk_name, I still get all occurances of each company.
Thanks, in advance, for your efforts!!!!!!!
John,