very complex left join problem

Looks like it should work, so might be data related.  What happens if you just run the subqueries ?

it doesn't run without the procedure even though i specify the dynamic parameter (@ScheduledSlotId) manually

so :

might be worthwhile doing select * from #SlotVideoItems into tmp_test_SlotVideoItems

declare @ScheduledSlotId  int     -- or varchar ?
set @ScheduledSlotId  = 9999    -- or something that does exist

--then

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

-- if you are expecting some results - and get nothing - then you have to start looking at #SlotVideoItems (another reason to select into tmp_test...)

hi mark
its a good idea but i am getting error when i run the query
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'into'.

select * from #SlotVideoItems into tmp_test_SlotVideoItems

declare @ScheduledSlotId  int     -- or varchar ?
set @ScheduledSlotId  = 36138-- or something that does exist

--then

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

Very sorry about that...

select * into tmp_test_SlotVideoItems from #SlotVideoItems

hi marks
thanks for that i really appreciate it but the problem is still there although i can see through.
do you think my logic is wrong i am gonna try to write again if it makes more sense

1 gameid columnn which works perfectly fne

2 gameidtwo new column which doesnt detect already selected items

logic is before it used to look into one cloumn now it should look into two different column
Gameid      Gameidtwo
G1      G5
G2      G10  -if g10 is selected then it shouldn't come back
G10      G12

Actually, I agree, the logic does look OK. I am questioning the data...

let me paste the whole block if it gives you some more insight i will try to grab the places where logic didn't work

  IF @VideoItemType = 1 -- segment  
   BEGIN  
--block one
   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 Game GS1  
--    ON VI.GameIDtwo = GS1.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 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 (Select VI5.GameIdtwo -- get the games already selected   
      from #SlotVideoItems S5   
      Join dbo.VideoItems VI5   
      ON  S5.[ScheduledSlotId] = @ScheduledSlotId  
       AND S5.[VideoItemID] = VI5.[VideoItemID]   
      Group by VI5.GameIdtwo)SV   
    ON  VI.GameIDtwo = SV.GameIDtwo
 
    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  SV.GameIDtwo is null  

Select allOpen in new window

i am attaching the result which means its not working

GameIdtwo
-----------
10000208
10000217 -- this one is selected prior to its selects again


GameId
-----------
10000154
10000174
10000206
10000217  -- this is coming back twice
10000222
10000335

do you think there is a conflict in both statement another thing i would like to mention is that not all the column in gameidtwo has values most of them are Null do you think that could cause a problem? on the other hand gameid has values in all the field

please ignore the line >>Also, noticed a "question alert" in my in box, and did a quick check: <<  it is referring to the "Could you please check" comment further down.

hi  mark
i am overwhelm with your response thankyou so much for your detail response i will prepare a document to explain the business logic /relationship.

i am reading through your response its kind of complex lol , and thankyou once again for your help

hi mark
Could you please clarify ?  This seems to be a triplicate question

1) https://www.experts-exchange.com/questions/23918190/very-complex-left-join-problem.html  (ie this one)
2) https://www.experts-exchange.com/questions/23915121/MS-SQL-Join.html
3) https://www.experts-exchange.com/questions/23924830/Combining-two-Joins-in-one-statement.html

i am not sure whether its a system alert or you are asking me to explain about these questions.
they are about the same problem so i have tried to re-write the questions to approach the problem different ways.

Yeah, sorry about the complex thought process - not sure how to have explained it any other way - but can see why it is not doing what you want, just not exactly sure how to make it do what you want, but will work on it...

Again, it is not the syntax of what you are doing, it is the logic, and data relationships...

The comment about the triplicate question is really to find out if it is in fact a triplicate and if so, then just to make other experts aware that there are in fact other discussions happening - sometimes just a keyword, or an insight, or a comment from a group of experts can trigger the solution.

Right now you seem to have three or four major experts - but only one on each thread. You might not be getting the attention you could be. Also, if I do find the solution here, and then post the answer on all three - while I would love the points, you can imagine how the other experts might feel about their efforts. I was just involved in a similar situation and one of the experts got a bit upset.

So, it is difficult - how to phrase the question, get it into the zones, get a consolidated perspective, get an answer as quick as possible, and at the same being patient when there are pressures to get an answer.  So, it might be worthwhile just advising the other two threads that there is a three way happening... Being courteous...

OK, have indented and aligned your query - does not seem to be a "closed" set - so missing a bit (or I screwed up) - and moved things around, did change the SHOWS SH link - it was hanging out there by itself...

The subqueries GN2, GM, ST, SV

GN2 seems to be a glorified "where" clause trying to establish selections where there is more than 3 genres
ST similar to GN2 except for 3 stories
GM seems to serve no real purpose at the moment - GAMEID is already there from that VIDEOITEM + #SLOTVIDEOITEMS join ealier so, not adding  or restricting any new information
SV seems to be similar to GM -  - GAMEIDTWO is already there from that VIDEOITEM + #SLOTVIDEOITEMS join ealier so, not adding or restricting any new information

Anyway, have a look at how I rearranged it a bit...

 IF @VideoItemType = 1 -- segment  
   BEGIN  
--block one
   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 Game GS1 ON VI.GameIDtwo = GS1.GameId  
    Join dbo.SubGenre SG ON GM1.SubGenreId = SG.SubGenreId  
    Join dbo.Genre GN ON SG.GenreId = GN.GenreId  
 
    LEFT JOIN #DayCounts DC ON VI.[VideoItemID] = DC.[VideoItemID] 
	Left JOIN DayCountingRun DD ON VI.[VideoItemID] = DD.[VideoItemID] --And DD.[VideoItemID]  is null --and DD.userid = @userid   
 
    LEFT JOIN dbo.ShowKeywords SK ON VIK.KeywordID = SK.KeywordID  
	JOIN shows SH ON SK.ShowID = SH.ParentShowId and SH.ShowID = @showid
 
    LEFT JOIN #SlotVideoItems S ON VI.VideoItemID = S.[VideoItemID] and S.[ScheduledSlotId] = @ScheduledSlotId 
 
    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  
 
 
    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 (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     
 
 
    LEFT JOIN (Select VI5.GameIdtwo -- get the games already selected   
               from #SlotVideoItems S5   
               Join dbo.VideoItems VI5   ON  S5.[ScheduledSlotId] = @ScheduledSlotId  AND S5.[VideoItemID] = VI5.[VideoItemID]   
               Group by VI5.GameIdtwo) SV   
 
               ON  VI.GameIDtwo = SV.GameIDtwo
 
 
    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  SV.GameIDtwo is null  

Select allOpen in new window

hi mark
thankyou for your response, i do appreciate your concern about posting the question to different places the i only reason i didnt cross-reference was simply not to confuse anyone otherwise i would have done it.
at this stage i wouldnt be so concerned about the Genre of gameidtwo as long as they are unique
i am gonna read through your valueable inputs and get back to you, i liked your previous thoughts about doing some sort of union (fascinating idea).
i think i would like to explain bit about the software so it may give you bit more insight.
the software is for scheduling system + it create shows automatically based on the keywords so each videoitem is assigned to specific keywords and stories and game.
games are categorised in the Genre and Sub-Genre so each game has a unique Genre and sub-Genre.
the code i sent you are basically sets of rules how to select the videoitems for each show.
show has two features 1)kind of stories can come in the show 2) kind of keywords can appear in the show
 the template of the show is defined in another table which basically outlines sequential order of each videoitems there are others which are different then videoitemtype =1
the rules i have sent applies to each show for instance normally its half an hour show it does this for the half an hour like copy the template into temporary table #SlotVideoItems  and then start putting the videoitems.

hi mark
i think there is something wrong with the approach i am taking i have been conducting some tests from the query you gave me i have added output for VI4.GameId as well i have attached the result as a gif image what i think i need is basically somehow comparing gameid value with gameidtwo if they match then block them to appear again so far i cant think of something.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp_test_SlotVideoItems]') 
AND type in (N'U'))
DROP TABLE [dbo].[tmp_test_SlotVideoItems]
 
declare @ScheduledSlotId  int     -- or varchar ?
set @ScheduledSlotId  = 36336
 
 
 
 
 
 
 
 
 
select * into tmp_test_SlotVideoItems from SlotVideoItems
Select VI4.GameIdtwo, VI4.GameId -- get the games already selected   
      from tmp_test_SlotVideoItems S4  
      Join dbo.VideoItems VI4   
      ON  S4.[ScheduledSlotId] = @ScheduledSlotId  
       AND S4.[VideoItemID] = VI4.[VideoItemID]   
	--And VI4.GameId = VI4.GameIdtwo  
     -- Group by VI4.GameIdtwo

Select allOpen in new window

result.GIF

hi mark
i am thinking if there is a way to establish that gameid and gameidtwo values are same i think that might an answer to the problem for instance if a query which will establish this and return the following example
gameidtwo = 10000196 gameid = 10000196

hi mark
finally i done it thanks to you by giving this union advice actually happen to become a soloution
i want to thank you going out of your way to help me
i have attached the working version of the code how i have used your idea

    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] 
		Union
		Select VI1.GameIdTwo -- 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, VI1.GameIdtwo) GM   
    ON  VI.GameID = GM.GameID
        LEFT JOIN (Select VI5.GameIdtwo -- get the games already selected   
      from #SlotVideoItems S5   
      Join dbo.VideoItems VI5   
      ON  S5.[ScheduledSlotId] = @ScheduledSlotId  
       AND S5.[VideoItemID] = VI5.[VideoItemID]   
Union
Select VI5.GameId -- get the games already selected   
      from #SlotVideoItems S5   
      Join dbo.VideoItems VI5   
      ON  S5.[ScheduledSlotId] = @ScheduledSlotId  
       AND S5.[VideoItemID] = VI5.[VideoItemID]  
      Group by VI5.GameIdtwo, GameId)SV   
    ON  VI.GameIDtwo = SV.GameIDtwo

Select allOpen in new window

really appreciate your help

Hi mattibutt, apologies for the silence - seems that e-mail notifications were down for a while...

hi mark
thats fine i hope you read my response because of your help i have solved this problem. nice meeting you