MS SQL Join

hi
i have a join which works perfectly fine but i want to reapply this onto the new field i have created in the database it doesnt seem to be working
new join my attempt
LEFT JOIN (Select VI1.GameIdtwo  
      from #SlotVideoItems S1  
      Join dbo.VideoItems VI1  
      ON  S1.[ScheduledSlotId] = @ScheduledSlotId  
       AND S1.[VideoItemID] = VI1.[VideoItemID]  
      Group by VI1.GameIdtwo) GS  
    ON  VI.GameIDtwo = GM.GameID

old join which is working
LEFT JOIN (Select VI1.GameId  
      from #SlotVideoItems S1   
      Join dbo.VideoItems VI1   
      ON  S1.[ScheduledSlotId] = @ScheduledSlotId  
       AND S1.[VideoItemID] = VI1.[VideoItemID]   
      Group by VI1.GameId) GM   
    ON  VI.GameID = GM.GameID

Open in new window

LVL 11
mattibuttAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

richard_cristCommented:
In your new join you call the subselect "GS" instead of "GM".  Is that the problem?

0
mattibuttAuthor Commented:
no but do u think GM can work for both join?
0
mattibuttAuthor Commented:
The correlation name 'GM' is specified multiple times in a FROM clause.  i am getting this error when i called my new join GM
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

ErnariashCommented:
You can called GS    or GM or anyother name the problem you are having is the VI is not the subset for your  GameIdtwo   thanks
  LEFT JOIN (Select VI1.GameIdtwo   
      from #SlotVideoItems S1   
      Join dbo.VideoItems VI1   
      ON  S1.[ScheduledSlotId] = @ScheduledSlotId  
       AND S1.[VideoItemID] = VI1.[VideoItemID]   
      Group by VI1.GameIdtwo) GS   
    ON  VI.GameID  = GS.GameIDtwo 

Open in new window

0
mattibuttAuthor Commented:
i am trying to see what you gave me works thing is i want to compare the gameid from the Game table in your recommendation you have joined it with the videoitem table
  ON  VI.GameID  = GS.GameIDtwo
0
mattibuttAuthor Commented:
it doesnt work
0
richard_cristCommented:
The only difference in your original entry at top was that:
1)  you changed GameID to GameIDtwo
2)  you changed the subselect GM to GS

Try the following:

LEFT JOIN (Select VI1.GameIdtwo  
      from #SlotVideoItems S1  
      Join dbo.VideoItems VI1  
      ON  S1.[ScheduledSlotId] = @ScheduledSlotId  
       AND S1.[VideoItemID] = VI1.[VideoItemID]  
      Group by VI1.GameIdtwo) GM  
    ON  VI.GameIDtwo = GM.GameID
0
richard_cristCommented:
Sorry, if that doesn't work try this:

LEFT JOIN (Select VI1.GameIdtwo  
      from #SlotVideoItems S1  
      Join dbo.VideoItems VI1  
      ON  S1.[ScheduledSlotId] = @ScheduledSlotId  
       AND S1.[VideoItemID] = VI1.[VideoItemID]  
      Group by VI1.GameIdtwo) GM  
    ON  VI.GameIDtwo = GM.GameIDtwo
0
ErnariashCommented:

Could you post your entire query? We cannot see from here VI and the columns? :)
Do you have a column GameIDtwo  on VI?  Do you want join GameIdtwo from GM  with  GameID on VI... We are guessing.
 
0
mattibuttAuthor Commented:
i was wrong it is not joined with game table its actually connected with the same table videoitem gameid with videoitem game id.
i am going to paste the entire snippet to see whether it makes more clearer the way its working

for richard i think the GM is already used previously so it can't be used again.

   Select Top 1 @sVideoItemID = VideoItemID ,@sVideoLength = VideoLength  
   FROM 
  (
    Select top 15 VI.VideoItemID ,VI.VideoLength  
    From dbo.VideoItems VI  
	Join UserSelectedSegments USVI 
	on  VI.VideoItemID = USVI.VideoItemID
    JOIN dbo.VideoItemsKeywords VIK  
    ON VI.VideoItemID = VIK.VideoItemID  
    Join Game GM1  
    ON VI.GameID = GM1.GameId  
    Join dbo.SubGenre SG  
    ON GM1.SubGenreId = SG.SubGenreId  
    Join dbo.Genre GN  
    ON SG.GenreId = GN.GenreId  
    LEFT JOIN ( Select GN1.GenreId -- -- get the generes of preselected segements  
      from #SlotVideoItems S3   
      Join dbo.VideoItems VI3   
      ON S3.[VideoItemID] = VI3.[VideoItemID]  
         AND VI3.VideoItemType =  @VideoItemType  
         AND S3.[ScheduledSlotId] = @ScheduledSlotId  
      Join Game GM1   
      ON VI3.GameID = GM1.GameId  
      Join dbo.SubGenre SG1  
      ON GM1.SubGenreId = SG1.SubGenreId  
      Join dbo.Genre GN1  
      ON SG1.GenreId = GN1.GenreId  
      Group by GN1.GenreId  
      Having Count(*) >= 3 ) GN2  
    ON GN.GenreId = GN2.GenreId  
	JOIN shows SH
	ON SH.ShowID = @showid
    LEFT JOIN dbo.ShowKeywords SK -- check keywords between segment and show  
    ON VIK.KeywordID = SK.KeywordID  
       AND SK.ShowID = SH.ParentShowId
    LEFT JOIN #SlotVideoItems S -- left join to make sure this video is not selected before in this slot  
    ON S.[ScheduledSlotId] = @ScheduledSlotId  
       AND VI.VideoItemID = S.[VideoItemID]   
    LEFT JOIN (Select VI1.GameId -- get the games already selected   
      from #SlotVideoItems S1   
      Join dbo.VideoItems VI1   
      ON  S1.[ScheduledSlotId] = @ScheduledSlotId  
       AND S1.[VideoItemID] = VI1.[VideoItemID]   
      Group by VI1.GameId) GM   
    ON  VI.GameID = GM.GameID 
  LEFT JOIN (Select VI1.GameIdtwo -- get the games already selected   -- this is the new join i am attempting to fix
      from #SlotVideoItems S1   
      Join dbo.VideoItems VI1   
      ON  S1.[ScheduledSlotId] = @ScheduledSlotId  
       AND S1.[VideoItemID] = VI1.[VideoItemID]   
      Group by VI1.GameIdtwo) GS   
    ON  VI.GameIDtwo = GM.GameID 
 
    LEFT JOIN (Select VI2.StoryID -- get the stories already selected ,applies only when only keywords in show  
      from #SlotVideoItems S2   
      Join dbo.VideoItems VI2   
      ON S2.[VideoItemID] = VI2.[VideoItemID]  
         AND VI2.VideoItemType =  @VideoItemType  
         AND S2.[ScheduledSlotId] = @ScheduledSlotId  
      WHERE @KeywordsOnlyShow = 1  
      Group by VI2.StoryID  
      Having Count(*) >= 3 ) ST  
    ON VI.StoryID = ST.StoryID   
    LEFT JOIN #DayCounts DC -- to see how many slots passed since this segment was selected  
    ON VI.[VideoItemID] = DC.[VideoItemID] 
	Left JOIN DayCountingRun DD -- to see how many slots passed since this segment was selected  
	 ON VI.[VideoItemID] = DD.[VideoItemID]
		--And	DD.[VideoItemID]  is null
		--and  DD.userid = @userid   
    WHERE   VI.VideoItemType = @VideoItemType and  USVI.UserId = @UserId and  USVI.Status=1
       AND S.[VideoItemID] is null -- make sure this segment unique in slot  
       AND GM.GameID is null -- make sure game is unique in slot 
		And  GS.GameIDtwo is null

Open in new window

0
mattibuttAuthor Commented:
hi
i still don't understand the problem i have tried so many different things but still no luck
0
richard_cristCommented:
In the SQL shown in your previous post you are missing a closing parentheses at the end.  That is, you have an open parentheses on line 3 but no closing parentheses at the end.  If that is the case in your actual SQL on your end then that might be the problem.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mattibuttAuthor Commented:
hi richard
richard i only pasted like relevant to this query there were other lines as well but not relevant to this issue
there is a closing parentheses

this is how is ends
      ) S
              Order by CHECKSUM(newid())

0
mattibuttAuthor Commented:
thing is everything else works perfectly fine but this new logic isnt working although i have been analysing the output of this query it is getting the information e.g i was given a suggestion by one expert on the following link
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23918190.html
i have been manage to see whether join works or no, it does work but i dont understand what is preventing it



declare @ScheduledSlotId  int     -- 
set @ScheduledSlotId  = 36136
 
 
 
 
select * into tmp_test_SlotVideoItems from SlotVideoItems
Select VI5.GameIdtwo -- get the games already selected   
      from tmp_test_SlotVideoItems S5   
      Join dbo.VideoItems VI5   
      ON  S5.[ScheduledSlotId] = @ScheduledSlotId  
       AND S5.[VideoItemID] = VI5.[VideoItemID]   
      Group by VI5.GameIdtwo

Open in new window

0
mattibuttAuthor Commented:
closing down the question cause i have asked too many similar QUESTIONS
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.