locked
Full outer joins RRS feed

  • Question

  • User29410129 posted
    Select COUNT(lp.like_fav_ID) as totallike,COUNT( dp.dislike_ID) as like, COUNT(c.comment_ID) as totalcomment
     from Resource r inner join Comments c on r.share_Resource_ID = c.share_Resource_ID
    full outer join LikePost lp on r.share_Resource_ID = lp.share_Resource_ID
    full outer join Dislike dp on r.share_Resource_ID = dp.share_Resource_ID
    where r.share_Resource_ID = 20

    I want to count record against share_resourceID = 20 . It will give me correct figure till 3 tables but when i add fourth table in join then it will not  give me correct result. tell me what's wrong with my joins? Waiting for your feedback

    Wednesday, May 25, 2016 11:56 AM

Answers

  • User-1636183269 posted

    Your query will not be give exact results which you are looking for. Please use below query

    Select
    (Select COUNT(lp.like_fav_ID) from LikePost lp Where lp.share_Resource_ID = r.share_Resource_ID) as totallike,
    (Select COUNT(dp.dislike_ID) from Dislike dp Where dp.share_Resource_ID = r.share_Resource_ID) as totallike,
    (Select COUNT(c.comment_ID) from Comments c Where c.share_Resource_ID = r.share_Resource_ID) as totallike
    From
    Resource r
    Where
    r.share_Resource_ID = 20

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 25, 2016 12:26 PM

All replies

  • User-1636183269 posted

    Your query will not be give exact results which you are looking for. Please use below query

    Select
    (Select COUNT(lp.like_fav_ID) from LikePost lp Where lp.share_Resource_ID = r.share_Resource_ID) as totallike,
    (Select COUNT(dp.dislike_ID) from Dislike dp Where dp.share_Resource_ID = r.share_Resource_ID) as totallike,
    (Select COUNT(c.comment_ID) from Comments c Where c.share_Resource_ID = r.share_Resource_ID) as totallike
    From
    Resource r
    Where
    r.share_Resource_ID = 20

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 25, 2016 12:26 PM
  • User77042963 posted

    Try LEFT JOIN instead of full outer join:

    Select COUNT(lp.like_fav_ID) as totallike,COUNT( dp.dislike_ID) as like, COUNT(c.comment_ID) as totalcomment
     from Resource r inner join Comments c on r.share_Resource_ID = c.share_Resource_ID
    Left join LikePost lp on r.share_Resource_ID = lp.share_Resource_ID
    Left join Dislike dp on r.share_Resource_ID = dp.share_Resource_ID
    where r.share_Resource_ID = 20

    Wednesday, May 25, 2016 5:34 PM
  • User29410129 posted

    Sorry dear, It's not working for my scenario.  Please  tell me how to solve this issue using joins? 

    Thursday, May 26, 2016 5:27 AM
  • User-219423983 posted

    Hi DanyalHaider,

    Sorry dear, It's not working for my scenario.  Please tell me how to solve this issue using joins? 

    I have made a demo as below and found the better way to achieve your needs is not use the Join, because the different results would be mixed in a same result table and it’s very not easy to calculate the count separately. Das.Sandeep’s suggestion would better for you to implement the need and the it would be more efficient.

    In the below demo, I provide a special situation that would be more than one "share_Resource_ID" values. It just shows this logic is not a good choice for this similar need.

    declare @mainTable table(MainId int, MainName varchar(20))
    insert @mainTable values (1,'AAA'),(1,'AAA-1'),(2,'BBB')
    
    DECLARE @tableA table(MainId int, TableAName varchar(20))
    insert @tableA values (1, 'TA-AA'),(1, 'TA-BB'),(2, 'TA-AA')
    
    DECLARE @tableB table(MainId int, TableBName varchar(20))
    insert @tableB values (1, 'TB-AA'),(2, 'TB-BB'),(2, 'TB-AA')
    
    DECLARE @tableC table(MainId int, TableCName varchar(20))
    insert @tableC values (1, 'TC-AA'),(1, 'TC-BB'),(1, 'TC-CC')
    
    select 
    	mt.MainId, MT.MainName, ta.TableAName, tb.TableBName, tc.TableCName
    from @mainTable MT
    	inner join @tableA ta on MT.MainId = ta.MainId
    	inner join @tableB tb on MT.MainId = tb.MainId
    	inner join @tableC tc on MT.MainId = tc.MainId 
    
    where MT.MainName like '%AAA%'
    

    The result

    MainId	MainName	TableAName	TableBName	TableCName
    1	AAA	TA-AA	TB-AA	TC-AA
    1	AAA	TA-AA	TB-AA	TC-BB
    1	AAA	TA-AA	TB-AA	TC-CC
    1	AAA	TA-BB	TB-AA	TC-AA
    1	AAA	TA-BB	TB-AA	TC-BB
    1	AAA	TA-BB	TB-AA	TC-CC
    1	AAA-1	TA-AA	TB-AA	TC-AA
    1	AAA-1	TA-AA	TB-AA	TC-BB
    1	AAA-1	TA-AA	TB-AA	TC-CC
    1	AAA-1	TA-BB	TB-AA	TC-AA
    1	AAA-1	TA-BB	TB-AA	TC-BB
    1	AAA-1	TA-BB	TB-AA	TC-CC
    

    Best Regards, <!--?xml:namespace prefix = "o" ns = "urn:schemas-microsoft-com:office:office" /--><o:p></o:p>

    Albert Zhang <o:p></o:p>

    Thursday, May 26, 2016 9:33 AM