Hello,

I have an sql statement problem that I hope one of you could help me with.
For a little backgorund info, I have two tables in my SQL Server 2000
database.  One table has distinct part numbers and descriptions.  The other
table has many rows of the same part numbers and additional information like
version number.  So between the two tables it is a many to one relationship.
Look at the example below.

Table One:
(Part Number Column)    (Description Column)
1234                                Widget One
1235                                Widget Two
1236                                Widget Three
1237                                Widget Four

Table Two:
(PartNumber Column)    (Additional Info Column)
1234                                Version One
1234                                Version Two
1234                                Version Three
1235                                Version One
1236                                Version One
1236                                Version Two
1237                                Version One

The above example was completely hypothetical.

Here is my solution to getting returned results back into one table
Here is the sql statement that I have:

SELECT DISTINCT productCatalog.productID, productCatalog.brandID,
productCatalog.partNumber, productCatalog.price, productCatalog.type,
productCatalog.imageURL, productCatalog.modifier, productCatalog.category,
Product_Applications.vehicleMake, Product_Applications.vehicleModel,
Product_Applications.vehicleYear, productCatalog.description FROM
dbo.productCatalog, dbo.Product_Applications WHERE productCatalog.partNumber
= Product_Applications.partNumber

When I run the above query I end up with duplicate partNumbers in my
returned results.  What I want are distinct part numbers based on the
different application types.  I am using these results to filter the
results.  So if someone selects a specific year and a specific type and a
specific make the results would be based on that filter.  This data is all I
have to work with.  Any suggestions?

Thanks,

J

Hi,

Do you mean that you need to display the Table1 discription in Table2 based
on the PartNumber column? If so, I think you might need to use the JOIN
statement.

SELECT Table2.PartNumber, Table2.AdditionalInfo, Table1.Description from
Table1 LEFT JOIN Table2 ON Table1.PartNumber=Table2.PartNumber

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Kevin,

Basically, I have one table (partsCatalog) that has all of my product
numbers with manufacturers, descriptions, prices, etc. and i have another
table (partsApplications) that has multiple rows that can correspond to one
product number in my partsCatalog table.  The relationship between the two
tables is made by the part number.  I am using the partsApplications table
to filter a query from the partsCatalog.

For Example:

A user want's to see all products based on a specific manufacturer.  So they
select a manufacturer and the results returned are all products from that
manufacturer.

The problem: (A user is not going to look erroniously through a list of
10,000 products to find their specific partnumber.)

The Solution: So the correct way would be to have several different options
to filter these results.  These options are by Year, Make, Model, Engine
Size, etc.  These options are not included in the partsCatalog table, but
they are included in the partsApplications table because there is a huge
amount of options that can represent just one part number.

My parts catalog table may have 10,000 rows in it (with distinct part
numbers. no two part numbers are the same) and my parts application table
may have 1,000,000 rows in it (with non distinct part numbers.  where there
could be 50 rows with the same part number).  When I use the join it
combines the total amount or rows so I end up with a query of 1,010,000 rows
where 90% of those rows are duplicate part numbers.

I want to get rid of the duplicate part numbers while still being able to
filter the part numbers based on all available options.

There!  Now did I confuse the hell out of you!!! LOL!

A perfect example would be like if you were to go to
http://www.zipzoomfly.com

If you browse through their products you can see that they have alot of
information to filter the results from therefore narrowing the results until
you have a much smaller amount of products to look at.

Below is a more detailed layout view of my two tables.

partsCatalog Table:

(partNumber)    (description)    (price)
001                    Part One         2.95
002                    Part Two         3.96
003                    Part Three       4.54
004                    Part Four        1.23

partsApplication Table:

(partNumber)    (year)     (make)    (model)      (engineSize)
001                    97         Ford        Explorer    V8
001                    98         Ford        Explorer    V8
001                    98         Ford        Explorer    V6
002                    02         Honda     Accord      V6
002                    03         Honda     Accord      V6
002                    04         Honda     Accord      4 Cylinder
002                    04         Honda     Accord      V6

And so on...


Thanks,

J



Show quoteHide quote

Hi,

I'm a little confused of your requirements. Please excuse me if there is
any misunderstanding. I think you need to transform the two tables to this:

(partNumber)    (year)     (make)    (model)      (engineSize)   
(description)
001                    97         Ford        Explorer    V8               
               Part One
001                    98         Ford        Explorer    V8               
               Part One
001                    98         Ford        Explorer    V6               
               Part One
002                    02         Honda     Accord      V6                 
            Part Two
002                    03         Honda     Accord      V6                 
            Part Two
002                    04         Honda     Accord      4 Cylinder         
      Part Two
002                    04         Honda     Accord      V6                 
            Part Two

So if a user selects Part Two, all the bottom 4 records will be displayed.
If so use

SELECT partsApplication.*, partsCatalog.description from partsApplication
LEFT JOIN partsCatalog ON
partsApplication.partNumber=partsCatalog.partNumber WHERE description =
'PartTwo'

If it's not the case, could you please post your desired result set here?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

You almost got it.  Transforming the result table to what you have below is
exactly correct.  I already have my sql returning those results.  That is
not my problem.

What I want to do is, instead of returning four records for part two.  I
just want to return one record for part two so that they know which part to
buy.  Right now with the sql statement that I have, I end up with a huge
result set becase there are more than one of the same part number in the
results.

For example base on your data below:

This is what would be returned:

(partNumber)    (year)     (make)    (model)      (engineSize)
(description)
002                    02         Honda     Accord      V6
Part Two
002                    03         Honda     Accord      V6
Part Two
002                    04         Honda     Accord      4 Cylinder
Part Two
002                    04         Honda     Accord      V6
Part Two

But I want it to be displayed is this:

(partNumber)     (description)
002                    Part Two

Here is the URL to my site.  You will see what I mean when you look at the
results.

http://www.tognottisautoworld.com/result.aspx?brand=290&c=Chevy%20Cams&b=true&mod=holley&v=filter

Thanks for your help I really appreciate it!

J


Show quoteHide quote

Sorry, I checked your site, but it makes me a little more confused. If you
already know the description is 'PartTwo' and you can just select it from
the partsCatalog table. It will return one record for PartTwo directly. If
you need to return the top most one in the result set, you can use

SELECT Top 1 partsApplication.*, partsCatalog.description from
partsApplication LEFT JOIN partsCatalog ON
partsApplication.partNumber=partsCatalog.partNumber WHERE description =
'PartTwo'

If you only need the partNumber and description part, just use

SELECT Top 1 partsApplication.partNumber, partsCatalog.description from
partsApplication LEFT JOIN partsCatalog ON
partsApplication.partNumber=partsCatalog.partNumber WHERE description =
'PartTwo'

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Updating Access database
Internet - Intranet Data Dilema
Inserting regional settings specific data into sql server
XML ADO Approach
multiple inserts or updates