The multi identifier couldn't be found error message?-CodePudding

I need to be able to add string "reviews" to database. I am required to work with subquery's. I need to combine "UserID" from the "User" table with the "OutfitID" from the "Outfit" table column. That way I can tell which review belongs to which User and to which outfit.

First I tried this query:

SELECT Gebruiker.GebrID,Outfit.ID 
FROM Gebruiker 
INNER JOIN Outfit ON Outfit.GebrID = Gebruiker.GebrID 
WHERE Gebruiker.Alias = 'john'
INSERT INTO Review(GebrID,OutfitID,StukTekst) 
VALUES(Gebruiker.GebrID, Outfit.ID,'ziet er cool uit');

Now I want to add the data to the review table but I get this error message:

This is my Database diagram:

CodePudding user response：

The more direct way to do this would avoid the values clause and the repeated scalar subqueries:

INSERT INTO Review (GebrID, OutfitID, StukTekst)
SELECT Gebruiker.GebrID, Outfit.ID, 'ziet er cool uit'
FROM Gebruiker INNER JOIN Outfit ON Outfit.GebrID = Gebruiker.GebrID 
WHERE Gebruiker.Alias = 'john';

As for whether the meets somebody's arbitrary "subqueries" requirement is unclear. If that query returns multiple rows then it will insert them all unless that causes a violation for the insert operation.

CodePudding user response：

If you are required to use subqueries, perhaps also use them.

You want to get values from user John, to insert into Review.

You could do this:

INSERT INTO Review(GebrID, OutfitID, StukTekst)
VALUES ((SELECT GebrID FROM Gebruikers WHERE Gebruiker.Alias = 'john'),
        (SELECT Outfit.ID FROM Outfit INNER JOIN Gebuikers ON Outfit.GebrID = Gebruikers.GebrID WHERE Gebruikers.Alias = 'john'),
        'ziet er cool uit'
       );

Now you're also using subqueries.