I am having a problem with building a sql query to populate a table. You guys will probably come up with a solution in under 60 seconds.
Suppose we have three tables: Table1, Table2, Table3.
In Table1, the columns are Auto_ndx, and Autos. In Table2, the columns are Tire_ID and Auto_ID. In Table3, the columns are Tire_ndx and TireType. In this example, Auto_ID = Auto_ndx, and Tire_ID = Tire_ndx
Table2 contains a list of every possible tire-auto combination, i.e:
1, 5
1, 8
1, 23
2, 11
2, 8,
2, 23
etc.
So, I have made a FPMI table, and I want the columns to represent the actual tire brand and auto model instead of the IDs. I can get stand-alone inner joins to work fine, but I can’t get everything to work at the same time. The resulting data should look like
Goodyear, Ford F150
Goodyear, Ford F250
Goodyear, Ford Escape
Bridgestone, Mercury Mountaineer
Bridgestone, Ford F250
Bridgestone, Ford Escape
meaning that Goodyear is Tire_ndx ‘1’ and Bridgestone is Tire_ndx ‘2’, Ford F150 is Auto_ndx ‘5’, etc. You get the idea.
I suppose I could add some columns and update Table2 periodically with a join, but that sort of defeats the purpose of a relational database. And, the whole point will be for my customer to be able to add rows to Table2 symbolically by selecting from a list of autos and tires from a couple of dropdown lists. See where I’m going with this?
Any ideas are appreciated as always.