Newbie: how to query multiple tables

[Leakyradiator]

I'm trying to set up a resource database for car restorers. I now have tables: Car_make, Car_model, Car_begin_year, Car_end_year, Company.

Some of the companies handle parts for (for example) 1967 to 1981 Camaros. Other's might have 1965 to 1980 Camaros.

I want my website visitor to be able to select in a dropdown box "Chevrolet", then in another dropdown "Camaro" and type in a text field "1968" and then be returned the companies that deal in this car.

I'm obviously encountering some many-to-many relationshops, here and probably need a junction table (or two) to do this properly, but don't fully understand how that is done. Any help is greatly appreciated.

 

[donutman]

Your tables should look something like this:

Company
CId int identity column
CompanyName varchar(30)
Addr1 varchar(30)
etc

CarMake
CMkId int identity column
MakeName varchar(30)
etc

CarModel
CMdId int identity column
ModelName varchar(30)
Year int
DateIntroduced datetime
etc

CompanyCar
CId int
CMkId int
CMdId int

Note you shouldn't use a Car_begin_year and end_year table. The year should just be part of the CarModel table. Even though (AFIK) there are no two car manufactures making the same model (with same name), it's probably reasonable to have a separate table for car make.
You make an entry in CompanyCar table for each company and for which cars they carry parts.
The only disadvantage of including the year in the CarModel table is that you have to be sure that you don't misspell the model name e.g. a 1967 Carmero and 1968 Carmro. The advantage is that it reduces the number of joins and simplifies the queries.
Your drop down box for make is populated with:
Select distinct MakeName from CarMake
Your drop down box for car model becomes a query on the CarModel table with a Where clause that restricts the selection to only the car make select in the previous dropdown box. And so forth. That should get you started.
-Karl



[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Leakyradiator]

Thanks Karl.
Wouldn't putting the year in the CarModel table require me to have an entry for each year, i.e., 1920 Ford, 1921 Ford, 1922 Ford, etc.?

 

[donutman]

True. You could put the 1st year and last year in the CarModel table. Yes, the more I think about it, that would be easier.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Leakyradiator]

The problem is, though, that one car company may supply parts for 1957 through 1960 Fords, while another may supply parts for 1959 through 1964 Fords. The dates can be different for each company.

 

[donutman]

You have two choices:
1) Put multiple rows of same model in CarModel table
2) Put only 1 year per model.
I would prefer #2 given the circumstance you suggest.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]