SQL Select Satement Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 Cams&b=true&mod=holley&v=filter

Thanks for your help I really appreciate it!

J
 
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."
 
Back
Top