CODEWITHSUNDEEP
phpmysqlinner-join
Mathew Jenkinson
Mathew Jenkinson

Reputation: 854

MySQL Insert into table with values from Inner Join

I'm trying to insert data into a inventory table, pulling in a UserID from a separate Users table to populate one of the fields.; 

 Inventory:
 ProductID | PurchasedByUser | OtherAttributes

and a table of Users;

 Users:
 DBID | UserActive | UserName
   1  |     1      |  mathew

So far my SQL looks like:

 INSERT INTO Inventory (ProductID, PurchasedByUser, OtherAttributes)
 SELECT 23, U.DBID, 'Yellow'
 FROM Inventory U INNER JOIN Users ud 
 ON U.DBID = ud.DBID AND ud.UserActive = 1 AND UserName = 'mathew'

Edit: I want to be able to make a single SQL INSERT request to add ProductID (INT), PurchasedByUser (Users.DBID), OtherAttributes (String) to the Inventory table without exposing the DBID and passing only the UserName field.

Upvotes: 2

Views: 10063

Answers (5)

AKS
AKS

Reputation: 55

In the Query U.DBID is wrong because U is alias of Inventory so please change and put ud.DBID and your query will be resolve.

INSERT INTO Inventory (ProductID, PurchasedByUser, OtherAttributes)
SELECT 23, ud.DBID, Yellow
FROM Inventory U INNER JOIN Users ud 
ON U.PurchasedByUser = ud.DBID AND ud.UserActive = 1 AND ud.UserName = 'mathew'

Upvotes: 1

Sujeet Sinha
Sujeet Sinha

Reputation: 2433

In the SQL query you framed, you are referring to DBID of U which is the Inventory table. From the structure you shared, I think you want the following query:

 INSERT INTO Inventory (ProductID, PurchasedByUser, OtherAttributes)
 SELECT 23, ud.DBID, Yellow
 FROM Inventory U INNER JOIN Users ud 
 ON U.PurchasedByUser= ud.DBID AND ud.UserActive = 1 AND UserName = 'mathew'

I'm still confused with what are trying to achieve here, you are fetching values from the Inventory and inserting it back again.. Not sure about that.. Just trying to help you figure out the query!

Upvotes: 0

AssenKhan
AssenKhan

Reputation: 566

CHANGE U.DBID TO ud.DBID

INSERT INTO Inventory (ProductID, PurchasedByUser, OtherAttributes)
SELECT 23, ud.DBID, 'Yellow'
FROM Inventory U INNER JOIN Users ud 
ON U.PurchasedByUser = ud.DBID AND ud.UserActive = 1 AND ud.UserName = 'mathew';

Upvotes: 1

Boby
Boby

Reputation: 1202

I believe 23 & Yellow are records And you need a quote for mathew. 

  INSERT INTO Inventory (ProductID, PurchasedByUser, OtherAttributes)
  SELECT 23, U.DBID, Yellow
  FROM Inventory U INNER JOIN Users ud 
  ON U.DBID = ud.DBID AND ud.UserActive = 1 AND UserName = mathew

To This

 INSERT INTO Inventory (ProductID, PurchasedByUser, OtherAttributes)
 SELECT ProductID, PurchasedByUser , OtherAttributes
 FROM Inventory U INNER JOIN Users ud 
 ON U.DBID = ud.DBID AND ud.UserActive = 1 AND UserName = 'mathew'

Upvotes: 0

dimlucas
dimlucas

Reputation: 5131

You can accomplish that in a two-step process. First get the id of the user:

SELECT DBID
FROM Users
WHERE UserActive=1 AND UserName='mathew';

Now that you have the user's id you can insert it into the Inventory table with a second query:

INSERT INTO Inventory (ProductID,PurchasedByUser,OtherAttributes )
VALUES(23,<THE-USER'S-ID>, 'Yellow');

I assume you know how to do this in PHP because you're not asking for PHP code

Upvotes: 0

Related Questions