Anilrgowda

I'll define a couple of tables to help clarify the question.
MainType
MainTypeID MainTypeName
1 car
2 truck
3 boat
SubType
SubTypeID MainTypeID SubTypeName
1 1 4-door
2 1 2-door
3 1 2-door hatchback
4 2 longbed
5 2 shortbed
TransportationUnit
TransUnitID MainTypeID SubTypeID Model CodeName
1 1 1 MyCar SecretCarName
2 1 3 Beater BrokenCar

What I want to do is enter data into the Transportation Unit table as simply as possible.
Via a web interface, the user selects the MainType and the SubTypeID by their respective names. The Model and CodeName are entered in simple text boxes. So, I want my query to look something like this
INSERT INTO TransportationUnit (MainTYpeID, SubTypeID, Model, CodeName)
Values (MainTypeID from MainTypeName, SubTypeID from SubTypeName, model, codename)

How do I format a single query such that the MainTypeID and SubTypeID are determined "on the fly" from the MainTypeName and SubTypeName? I'm guessing that I need some kind of INNER JOIN, but I'm having a heck of a time getting it right.

------------------

Anilrgowda

I don't know if you need a join, since the mainTypeID is already stored in the SubType table:

INSERT INTO TransportationUnit (MainTYpeID, SubTypeID, Model, CodeName)
SELECT st.MainTypeID, st.SubTypeID, 'The Model Entered', 'TheCodeName Entered'
FROM SubType st
WHERE st.SubTypeName = 'selected sub type name'

But you could also do this:

INSERT INTO TransportationUnit (MainTYpeID, SubTypeID, Model, CodeName)
SELECT st.MainTypeID, st.SubTypeID, 'The Model Entered', 'TheCodeName Entered'
FROM SubType st INNER JOIN MainType mt
ON st.MainTypeID = mt.MainTypeID
WHERE st.SubTypeName = 'selected sub type name'

------------------