--===== I believe that this is all the information you would like. I apologize for the length of this post.
--===== NEW TABLE TO LINK THE MOVIES AND THE CAST MEMBERS. Stores the Movie Name and the Star ID
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#MovieAndStar','U') IS NOT NULL
DROP TABLE #MovieAndStar
--===== Create the test table with
CREATE TABLE #MovieAndStar
(
--===== THE ID Is automatically generated in the real table
ID uniqueidentifier PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
Movie_Name_Full varchar(100), -- Is the Name of the Movie and the Primary Key in the Movie Table
StarID uniqueidentifier --Is automatically generated in the Starring Table as the Primary Key
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #MovieAndStar ON
--===== Insert the test data into the test table
INSERT INTO #MovieAndStar
(ID, Movie_Name_Full, StarID)
SELECT 'f5fcc4a4-f820-4c2d-a030-02f71fcc2daf','Speed','e6e0159e-4085-47d5-8920-2fe9bb996a57' UNION ALL
SELECT '639c2354-88d6-4e65-9da8-0653dbb90556','Speed','7d790ac4-a9f9-42d1-a380-356e349459b5' UNION ALL
SELECT 'a1a2be40-d2f4-4334-bad3-27a5585e97f6','The Blind Side','e6e0159e-4085-47d5-8920-2fe9bb996a57' UNION ALL
SELECT 'af6557c0-6541-404b-880a-190afeb122c6','The Matrix','7d790ac4-a9f9-42d1-a380-356e349459b5' UNION ALL
SELECT 'fa0054c7-4a12-401a-9cff-26bc41fd34dc','The Matrix','f94116b9-42ed-4231-afd9-46b7fe15a635' UNION ALL
SELECT '002b1d3d-fbd3-4b39-8da8-3dafcf8a3638','The Matrix Reloaded','7d790ac4-a9f9-42d1-a380-356e349459b5' UNION ALL
SELECT 'e6f2f088-2436-4292-805f-5260033f833f','The Matrix Reloaded','f94116b9-42ed-4231-afd9-46b7fe15a635' UNION ALL
SELECT '23967285-4fa8-4449-a0e4-5a24fc1e0ce2','The Matrix Revolutions','7d790ac4-a9f9-42d1-a380-356e349459b5' UNION ALL
SELECT '23967285-4fa8-4449-a0e4-5a24fc1e0ce2','The Matrix Revolutions','f94116b9-42ed-4231-afd9-46b7fe15a635' UNION ALL
SELECT '9fb2688c-b351-4a28-b341-36d986e10107','CSI','f94116b9-42ed-4231-afd9-46b7fe15a635'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #MovieAndStar OFF
--========== NEW TABLE FOR CAST MEMBERS ==========
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Starring','U') IS NOT NULL
DROP TABLE #Starring
--===== Create the test table with
CREATE TABLE #Starring
(
--===== The StarID is automaticaly generated in the real table
StarID uniqueidentifier PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
Star_FName varchar(25),
Star_LName varchar(25),
Star_Image varchar(50),
Star_Bio varchar(5000),
Star_Website varchar(100)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Starring ON
--===== Insert the test data into the test table
INSERT INTO #Starring
(StarID, Star_FName, Star_LName, Star_Image, Star_Bio, Star_Website)
SELECT 'e6e0159e-4085-47d5-8920-2fe9bb996a57','Sandra','Bullock,'sandra.jpg','
Sandra Bullocks biography.
Sandra Bullocks biography.','www.google.com' UNION ALL
SELECT '7d790ac4-a9f9-42d1-a380-356e349459b5','Keanu','Reeves','keanu.jpg','
Keanu Reeves biography.
Keanu Reeves biography.','www.google.com' UNION ALL
SELECT 'f94116b9-42ed-4231-afd9-46b7fe15a635','Laurence','Fishburne','laurence.jpg','
Laurence Fishburnes biography.
Laurence Fishburnes biography.','www.google.com'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Starring OFF
--========== NEW MOVIE TABLE ==========
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Movie','U') IS NOT NULL
DROP TABLE #Movie
--===== Create the test table with
CREATE TABLE #Movie
(
--===== The MovieID is automatically generated in the real table
MovieID uniqueidentifier, --Is an IDENTITY column on real table
Movie_Name_Full varchar(100) PRIMARY KEY CLUSTERED -- etc. No need to put the rest of the info in becuase it's not needed for this problem
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Movie ON
--===== Insert the test data into the test table
INSERT INTO #Movie
(MovieID, Movie_Name_Full)
SELECT '413d899c-2075-4714-aaf1-b33878f26cfb','Speed' UNION ALL
SELECT '5ab9e518-102b-4e72-a7e1-6e6343302d50','The Blind Side' UNION ALL
SELECT '8e76585b-3812-4a0d-a00b-3f1882999345','The Matrix' UNION ALL
SELECT '8609e73f-d9cb-4017-aa38-5db80ea99998','The Matrix Reloaded' UNION ALL
SELECT '0f39ca8d-620a-4681-8a75-9657649d4314','The Matrix Revolutions' UNION ALL
SELECT '116b258e-0b4e-4770-995a-e0df89e803f6','CSI'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Movie OFF
--========== BELOW is my current SQL Statement, I am passing the @MovieNameFull from asp.net (VB)
--========== This code also includes some SQL code I tried but couldn't get to work
--========== It's painfully obvious that I have no idea what I am doing. Thanks for your understanding.
@Movie_Name_Full varchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
--DECLARE @StarID uniqueidentifier
--SELECT @StarID = StarID FROM dbo.MovieAndStar WHERE Movie_Name_Full = @MovieNameFull
-- SELECT Cast and Information about them
BEGIN
SELECT MovieAndStar.Movie_Name_Full, MovieAndStar.StarID, Star_FName, Star_LName, Star_Image, Star_Bio, Star_Website --, Movie.MovieID
FROM dbo.MovieAndStar INNER JOIN dbo.Starring
ON MovieAndStar.StarID = Starring.CastID
--JOIN dbo.Movie ON MovieAndStar.Movie_Name_Full = Movie.Movie_Name_Full
WHERE MovieAndStar.Movie_Name_Full = @MovieNameFull AND MovieAndStar.StarID = Starring.StarID
--BEGIN
-- SELECT MovieAndStar.StarID, ID FROM dbo.MovieAndStar INNER JOIN dbo.Starring
-- ON MovieAndStar.StarID = Starring.StarID
-- WHERE MovieAndStar.Movie_Name_Full = @MovieNameFull AND MovieAndStar.StarID = Starring.StarID
-- IF StarID has a value Get the Movies for the Matching Cast Member
--IF @StarID IS NOT NULL
-- IF MovieAndStar.StarID IS NOT NULL
--BEGIN
--SELECT dbo.MovieAndStar.Movie_Name_Full, dbo.Movie.MovieID FROM dbo.MovieAndStar
--INNER JOIN dbo.Movie ON dbo.MovieAndStar.Movie_Name_Full = dbo.Movie.Movie_Name_Full
--WHERE dbo.MovieAndStar.StarID = @StarID
--ORDER BY Movie_Name_Full
--END
END
END
--========== THE OUTPUT SO FAR ==========
--======= movie.aspx page
--======= Movie Page
-- <!-- This is the Section where the code above is used -->
-- Two Columns EXAMPLE OF THE LAYOUT
-- Star_FName Star_LName Star_Bio
-- Star_Image Star_Bio Continued...
-- Star_Website
-- Other Movies that Cast Member Appears in
-- <a href="movie.aspx?MovieID=MovieID">Movie_Name_Full</a>
-- Example for the movie SPEED
--Cast Members of Speed
--Sandra Bullock Sandra Bullocks biography.
--sandra.jpg Sandra Bullocks biography.
-- Movies Sandra appears in:
-- NOTE: the above SQL Statement currently gives me the name of only one movie (the one for the current page)
-- Speed
-- NOTE: What I want is this: (ALL The Movie Names from the MovieAndStar AND the MovieID from the Movie Table
-- <a href="movie.aspx?MovieID=413d899c-2075-4714-aaf1-b33878f26cfb">Speed</a>
-- <a href="movie.aspx?MovieID=5ab9e518-102b-4e72-a7e1-6e6343302d50">The Blind Side</a>
--Keanu Reeves Keanu Reeves biography.
--keanu.jpg Keanu Reeves biography.
-- Movies Keanu appears in:
-- <a href="movie.aspx?MovieID=413d899c-2075-4714-aaf1-b33878f26cfb">Speed</a>
-- <a href="movie.aspx?MovieID=8e76585b-3812-4a0d-a00b-3f1882999345">The Matrix</a>
-- <a href="movie.aspx?MovieID=8609e73f-d9cb-4017-aa38-5db80ea99998">The Matrix Reloaded</a>
-- <a href="movie.aspx?MovieID=0f39ca8d-620a-4681-8a75-9657649d4314">The Matrix Revolutions</a>