Combining two Joins in one statement

A LEFT JOIN should be used when you always want results from the left side even if there isn't a match in the right side (in your case the queries labelled GM and SV)

If you want to do intersections then use the NOT IN or IN clause.  NOTE:  NOT IN is an expensive operation.  It requires a full scan of the tables.

experts : please be aware that this could be a triplicate

1) https://www.experts-exchange.com/questions/23918190/very-complex-left-join-problem.html
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   (this one)

mattibutt : could you please clarify ?

hi tedbilly
i have read your thoughts thankyou, i am trying to understand what you sent me i am gonna write my response to see whether i understood you clearly

i have tried this query as well yesterday but it didnt produce any result either
AND NOT EXISTS( SELECT NULL FROM VideoItems VI
--                 WHERE       SV.GameIDtwo = VI.[VideoItemID]


now i think what i understood by your suggestion is that i run your queries which make sure that gameid and gameidtwo are not already selected?
so i will not use the left join clauses for both GM and SV ? i take them out?

thanks

--(I Keep Both of these queries)?
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
AND GM.GameID is null 
 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
AND SV.GameID is null
 
-- then i add these queries as well?
WHERE
  VI.GameID NOT IN (Select VI1.GameId 
                          from #SlotVideoItems S1   
                          Join dbo.VideoItems VI1   
                          ON  S1.[ScheduledSlotId] = @ScheduledSlotId  
                           AND S1.[VideoItemID] = VI1.[VideoItemID]   
                          Group by VI1.GameId)
 
WHERE
 VI.GameIDtwo NOT IN (Select VI5.GameIdtwo 
      from #SlotVideoItems S5   
      Join dbo.VideoItems VI5   
      ON  S5.[ScheduledSlotId] = @ScheduledSlotId  
       AND S5.[VideoItemID] = VI5.[VideoItemID]   
      Group by VI5.GameIdtwo)

Select allOpen in new window

hi
i think my previous post maybe a bit confusing are you suggesting that i only use the following codes?
 VI.GameID NOT IN (Select VI1.GameId
                          from #SlotVideoItems S1  
                          Join dbo.VideoItems VI1  
                          ON  S1.[ScheduledSlotId] = @ScheduledSlotId  
                           AND S1.[VideoItemID] = VI1.[VideoItemID]  
                          Group by VI1.GameId)
 
WHERE
 VI.GameIDtwo NOT IN (Select VI5.GameIdtwo
      from #SlotVideoItems S5  
      Join dbo.VideoItems VI5  
      ON  S5.[ScheduledSlotId] = @ScheduledSlotId  
       AND S5.[VideoItemID] = VI5.[VideoItemID]  
      Group by VI5.GameIdtwo)

I'm not clear what you are trying to achieve.  I'm sorry but your requirements in the question aren't clear.

From you're code it looked like you wanted to find items that are in one list but not the other.  However, it now seems like you want to find matches or possible duplicates?  If yes, then we need another query that would be far simpler.

hi Tedbilly
sorry if i confused you, i am only looking for unique values , let me write below
gameid gameidtwo
g1              g5
g2              g4
g3
g4
g5

expected output
g1 + g2 + g3
g4 and g5 shouldnt come because they are already selected either at gameid or gameidtwo stage
i havent tested your codes yet i will do it tomorrow
what my assumption is that when i run the below it will only select unique values ? if it does that then this is what i want to acheive.
VI.GameID NOT IN (Select VI1.GameId
                          from #SlotVideoItems S1  
                          Join dbo.VideoItems VI1  
                          ON  S1.[ScheduledSlotId] = @ScheduledSlotId  
                           AND S1.[VideoItemID] = VI1.[VideoItemID]  
                          Group by VI1.GameId)
 
WHERE
 VI.GameIDtwo NOT IN (Select VI5.GameIdtwo
      from #SlotVideoItems S5  
      Join dbo.VideoItems VI5  
      ON  S5.[ScheduledSlotId] = @ScheduledSlotId  
       AND S5.[VideoItemID] = VI5.[VideoItemID]  
      Group by VI5.GameIdtwo)

hi Tedbilly
i have tried to run your query it actually doesnt even let me select one videoitem, i have tried to run it with and without the existing joins but it doesn't work at all

And VI.GameID NOT IN (Select VI1.GameId 
                          from #SlotVideoItems S1   
                          Join dbo.VideoItems VI1   
                          ON  S1.[ScheduledSlotId] = @ScheduledSlotId  
                           AND S1.[VideoItemID] = VI1.[VideoItemID]   
						
                          Group by VI1.GameId)
	 And VI.GameIDtwo NOT IN (Select VI5.GameIdtwo 
      from #SlotVideoItems S5   
      Join dbo.VideoItems VI5   
      ON  S5.[ScheduledSlotId] = @ScheduledSlotId  
       AND S5.[VideoItemID] = VI5.[VideoItemID] 
		
      Group by VI5.GameIdtwo) 

Select allOpen in new window

i have run your query in isolation it doesnt actually select anything at all however if i run without "NOT" then it returns the results.

i found another approach by using the union but thanks for your help

Hmm I appreciate the points but based on reading your last response a union should be used.  If you had given me the schema for the table(s) I could have answered it correctly.

Hi tedbilly, that is exactly what mattibutt ended up doing : https://www.experts-exchange.com/questions/23918190/very-complex-left-join-problem.html?cid=238&anchorAnswerId=23029738#a23029738

Quick question for you - have you been plagued with some ee mail problems ? seems my notifications are running between 12 and 24 hours late ...

Oops, actually I typed that last comment wrong.  I don't think he need a union at all.  I have a feeling that whole query could have be reduced into a NOT IN statement but without the schema it was hard to read.  Oh well.

Hmm you are correct.  There is some big time gaps.  I just received an email that the body states 1am my time and arrived at 8pm!

There is something else odd as well.  I can't view my 'Zone Rank'  It says I'm not ranked!

Hi tedbilly: there are a few reported anomolies going on - the EE team are working on fixing it, but still not quite right. I am told at these times that patience is a virtue - a virtue that has already seen me loose out on many thousands of points.