Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select Statement passing ID to Next Select Statement


Select Statement passing ID to Next Select Statement

Author
Message
jasonknarr
jasonknarr
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1465
Hello,

I'm stuck after looking online and reading for 3 days, I would really appreciate some help.

I have a website with a bunch of Movies. So far, I have successfully been able to populated the movie page with everything including a full list of cast members. However, I can't figure out how to grab all the movies that each cast member appears in to go with the list of actors on the individual movie page.

Here is my current code, including some of the code I tried before:

BEGIN
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.StarID
--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

This code does give me all the cast for the movie, but I need all the movies that each cast member appears in. There are three tables that I need info from m-ID from the Movie table, MovieNameFull, StarID from the MandA Table and the Star Name, Biography and Website, etc. information from the S table.

Thanks in advance for your help and/or recommendations!
ColdCoffee
ColdCoffee
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2557 Visits: 5547
Hi Buddy,how about you going through this following article and helping us help you?? :-)

CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN

When u do so, i am sure a lot of us will help u instantly...
So please post

1. Table Structures - CREATE TABLE scripts
2. Constraints or Indexes in the table, - CREATE SCRIPTS
3. SAMPLE DATA - INSERT INTO TABLE scripts
4. Desired output - some visual representation of this.

We could guess very little information from your post mate Sad !!
jasonknarr
jasonknarr
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1465
Thank you! I will read the post and update my request accordingly.
jasonknarr
jasonknarr
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1465
--===== 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.
-- www.google.com
-- 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.
-- www.google.com
-- 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>
LutzM
LutzM
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7587 Visits: 13559
Please try to run your code in TempDB. You'll find that it's not runable at all, even after 10 or so corrections...



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
jasonknarr
jasonknarr
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1465
Sorry about that. I will do my best to fix it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search