Need help with INSERT...SELECT...WHERE

[shanetastic]

Here is my query:

 

INSERT INTO Territories 
(RepId, PrincipalID, StateID)
SELECT Reps.RepID
Where Reps.RepName = 'ARepName'
SELECT Principals.PrincipalID
Where Principals.PrincipalName = 'APrincipalName'
SELECT States.StateID
Where States.StateAbbreviation = 'LA'

 

I have four tables:

 

Reps
------
RepID
RepName


Principals
-----------
PrincipalID
PrincipalName


States
--------
StateID
StateAbbreviation

Territories
------------
RepID
PrincipalID
StateID

 

I know the RepName, PrincipalName, and StateAbbreviation.  I am trying to use those to pull the respective IDs from the respective tables and then insert a row into Territories.

 

What am I doing wrong?

[fenway]

You need to JOIN those other tables first.

[shanetastic]

Am I getting warmer?

 

INSERT INTO Territories 
(RepId, PrincipalID, StateID)
JOIN StatesON Territories.StateID = States.StateID
JOIN Reps ON Territories.RepID = Reps.RepID
JOIN Principals ON Territories.PrincipalID = Principals.PrincipalID
SELECT Reps.RepID as RepID
Where Reps.RepName = 'ARepName'
SELECT Principals.PrincipalID as PrincipalID
Where Principals.PrincipalName = 'APrincipalName'
SELECT States.StateID as StateID
Where States.StateAbbreviation LIKE '%LA%'

[awjudd]

You are inserting into a mapping table so chances are you may not have that data in the application as of yet.

 

INSERT INTO Territories ( RepID, PrincipalID, StateID )
     SELECT ( SELECT RepID FROM Reps WHERE RepName = 'ARepName' ) AS RepID
         , ( SELECT PrincipalID FROM Principals WHERE PrincipalName = 'APrincipalName' ) AS PrincipalID
         , ( SELECT StateID FROM States WHERE StateAbbreviation LIKE '%LA%' ) AS StateID

 

Assuming they all return 1 row.  If they don't then you will need a bit more for the query to get it to work.

 

~juddster

[shanetastic]

Worked perfectly, thanks!