# very complex left join problem

hi
problem scenario
i have stored procedure which select unique videoitem for a specify timeline, it also make sure that game is unique as well but now i have scenario where one videoitem is about two games so i need to still preserves the previous role and apply it to the new situation i have created another field in the videoitems table which is called gameidtwo and assigned 2nd game of videoitem so if either game is already selected it shouldn't return as previously it worked for one game

code details

i have a join which selects unqiue record

i want to use this soloution again for another new field i have created in the database
join i am currently trying to fix is given below

problem is even though i replicated working version its still not doing what i expect it to be.

the following difference exist between the two
in the selection statement the join is created between Videoitem.Gameid and Game.Gameid

Join Game GM1
ON VI.GameID = GM1.GameId

i dont understand what can be done to sort out the problem.
solution works
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

solution doesn't work
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
LVL 11
###### Who is Participating?

Yes, the combinations of the way in which you are selecting will mean it will come back twice - from the first left join and then again from the second left join. From my understanding, then you are only really wanting those GameID that are not part of the selection already, but are included by their relationship to gameid in the first join. Which basically means you need to establish that selection criteria first, and then use that to help get the second instances - or - in the second left join, look up to see if in fact the title does not already exist in the first selection - but with the amount of embedded subqueries that could be a very difficult thing to sort out.

It is almost as if you want a union of the two gamied's and then do a left join to that... e.g.

left join
(
select gamid, and other stuff from subquery1
union            -- use union will only get other rows that don't already exist - use union all to return rows that might already exist.
select gamid, and other stuff from subquery2
) as games on games.gameid = vi.gameid

Also, noticed a "question alert" in my in box, and did a quick check :
I think it might be an idea to look at the full query you already have - annotate what each subquery is trying to achieve and have a long hard look at rationalising all those joins on sub-queries. Might be best to establish one query which does all the baseline information  then branch out from there e.g.

select interesting stuff from
(
select * from this and that join here and there outer join other stuff
) as baseline

then

select interesting stuff, additional information from
(
select * from this and that join here and there outer join other stuff
) as baseline

It does seem that each new part of the puzzle is coming in as an additional left join and that is really duplicating some rows where you are not wanting it...

I will try to sift through that query - but cvould you please annotate it a bit more to help describe some of the business logic / data logic and relationships ? The syntax of the query is not the problem, it is the data relationships that it is forming, so from a "tech" point of view, it will run, but from a user perspective it does not give the desired results - and that is where we need your insight - not so much the code, but the data relationships that need to be established.

Makes sense ?

Also,

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

1) http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23918190.html  (ie this one)
2) http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23915121.html
3) http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23924830.html

0

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

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

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...)
0

Author Commented:
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
0

select * into tmp_test_SlotVideoItems from #SlotVideoItems
0

Author Commented:
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

0

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

Author Commented:
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
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
0

Author Commented:
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

0

Author Commented:
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
0

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

Author Commented:
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
0

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

1) http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23918190.html  (ie this one)
2) http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23915121.html
3) http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23924830.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.
0

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

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

one thing that does seem to be missing are the genre's from gameidtwo...

In fact, it would appear that all we are interested in is the top 15  videoitemid and length for those videitems of type : @VideoItemType and for selected segments from @userid (and that status is 1) which have a story > 2 and genre > 2

Trying to get the "unique" entries by checking where it is null is probably adding to the issue at hand...

So, is it important that genre is game specific ? for example if  "genre 1" came from  gameid and "genre 1" came from gameid2 then do we count that as 2 for "genre 1" so long as gameid and gameid2 are unique ?

0

Author Commented:
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.

0

Author Commented:
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
result.GIF
0

Author Commented:
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
0

Author Commented:
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
0

Author Commented:
0

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

0

Author Commented:
hi mark
thats fine i hope you read my response because of your help i have solved this problem. nice meeting you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.