Clash Royale CLAN TAG#URR8PPP

SQL Server - Trying to join 2 foreign keys from 1 primary key in a procedure

Trying to join 2 foreign keys from 1 primary key in a procedure: Its very hard for me, and I want to make a code that will be simple. sitting for hours of hours from evening until morning and didn't find any answer to this issue. very hard. In mySQL there is an answer on this site but it doesn't work.

I must insert the each primary key (there are 2 in two different tables) to different tables. I tried with the "view" option but it didn't work.

My procedure is to write a story and insert an image, each one of them has ID.

CREATE PROCEDURE UserStory
@UserID [INT],
@story [NVARCHAR] (120),
@img [VARCHAR](MAX)
AS
BEGIN
INSERT INTO dbo.Stories (StoryText)
VALUES (@story)

INSERT INTO dbo.Images (img)
VALUES (@img)

SELECT
imagesInStories.imgID3, imagesInStories.StoryID3,
Stories.imgID2, Users_Stories.ID2, Users_Stories.StoryID2
FROM
Images B3, Stories T1, Table_Users
INNER JOIN
imagesInStories G1 ON B3.imgID3 = G1.imgID
INNER jOIN
imagesInStories G2 ON B3.imgID2 = G2.imgI
END


Why does this code cause errors? erorr...could not be found (I'll use Stories T1 and Table_Users later) right now the error just ruin it all...lol

My mission here is to insert the primary to two foreign key from two different tables (could be more if so..) .

The tables I use:

[dbo].[Images] , [Table_Users], [dbo].[Stories], [Users_Stories],


[imagesInStories] each has those IDs.

[imagesInStories]

If any complain, tell me and I'll fix the writing and question.

P.S

I changed each key to have it's own unique name as ID2 and ID3 not same as

Images.ID, Stories.ID and/or imagesInStories.imgID, Stories.imgID


Is it necessary?

Thanks
E.

temp

temp

JOIN

2 Answers
2

if you have assign alias to a table, you must use that and can't use the table name anymore

SELECT
imagesInStories.imgID3, imagesInStories.StoryID3,
Stories.imgID2, Users_Stories.ID2, Users_Stories.StoryID2
FROM
Images B3, Stories T1, Table_Users
INNER JOIN
imagesInStories G1 ON B3.imgID3 = G1.imgID
INNER jOIN
imagesInStories G2 ON B3.imgID2 = G2.imgI


so in the above query imagesInStories.imgID3 should be either G1.imgID3 or G2.imgID3

imagesInStories.imgID3

G1.imgID3

G2.imgID3

and the other column also.

Note : please avoid the comma notation in FROM and use proper ANSI JOIN style

FROM

JOIN

I tried this code - but! it does not insert the primary keys to the tables with the foreign keys... can you please tell me why?

CREATE proc UserStory
@UserID [int],
@story [nvarchar] (120),
@img [varchar] (max)
AS
BEGIN
Insert into Stories ([StoryText]) values (@story)

Insert into Images ([img]) values (@img)
SELECT dbo.Table_Users.UserID,
dbo.Users_Stories.*,
dbo.Stories.StoryID,
dbo.Stories.imgID2,
dbo.imagesInStories.*,
dbo.Images.*
FROM dbo.Images
INNER JOIN dbo.imagesInStories
ON dbo.Images.imgID = dbo.imagesInStories.imgID3
INNER JOIN dbo.Stories
ON dbo.Images.imgID = dbo.Stories.imgID2
AND dbo.imagesInStories.StoryID3 = dbo.Stories.StoryID
INNER JOIN dbo.Users_Stories
ON dbo.Stories.StoryID = dbo.Users_Stories.StoryID2
INNER JOIN dbo.Table_Users
ON dbo.Users_Stories.ID2 = dbo.Table_Users.UserID
WHERE dbo.Table_Users.UserID = @UserID
END
GO


wish to end this and continue with my next sql proc.
Thanks

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.