• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

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

0
mattibutt
Asked:
mattibutt
  • 9
  • 4
  • 2
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now