SQL statement that restricts redundant queries? 1

[95lightning]

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:

&quot;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&quot;

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,

 

[foxdev]

John, I racked my feeble brain for quite a while, and every time I thought I had a solution (subselects, unions, etc), I found them to not satisfy the problem.

I know this is probably out of the question, but a solution (well, prevention of the problem) would be normalizing the two tables, so that you'd have a ParentCompany and CompanyLocation tables, a 1-many relationship between the two. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org

 

[Andel]

John, can you provide a sample data (10 records or so) and your desired query result.
Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[Andel]

Hi John,

Try this simple query.

select pk_name, name, city, state, cntry
from gt_name a
where pk_name = (select top 1 pk_name from gt_name b where a.name = b.name)

Andel
andelbarroga@hotmail.com

http://andyb.8m.com