Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to show record even if it doesn't exist in the table that was joining

Answered How to show record even if it doesn't exist in the table that was joining

  • Wednesday, February 06, 2013 1:22 AM
     
     
    Sign In to Vote
    0

    Hi to all,

    Please bear with my title.. anyway, im a little bit confuse here, please see my sample table below

    StaffTable

    StaffID StaffFirstName StaffLastName StaffDesignation

    1          Steve                  Johnson               Mgr

    2          Joey                    Smith                   Mgr

    3          John                    Cruz                     AMgr

    4         Ian                       Tayao                   AMgr

    5          Jimmy                  Douglas                Mgr

    TimeTable

    Date               StaffID      CustomerID

    2013-01-01     1                 1

    2013-01-01     1                  2

    2013-01-01     2                 1

    2013-01-05     5                5

    2013-01-05     3                 4

    2013-01-01     5                 3

    2013-01-10     4                 9

    Sample result that i want is

    Date                 Steve      Joey    John     Ian    Jimmy

    2013-01-01       2            1          0          0         1

    2013-01-05       0            0          1          0         1

    2013-01-10       0            0          0          1         0

    i dont know if this is possible but if not something like this:

    Date               Staff

    2013-01-01     Steve            2

                           Joey              1

                           John              0

                           Ian                0

                           Jimmy            1

    2013-01-05     Steve             0

                           Joey               0

                           John              1

                           Ian                0

                           Jimmy            1

    2013-01-10    Steve             0

                           Joey              0

                           John              0

                           Ian                1

                           Jimmy            0

    the thing is i dont know why if the record has 0 record it will not show that particular staff

    it will only show the staff that has a record on the time table

    i only get below result ex:

    2013-01-01     Steve            2

                           Joey              1

                           Jimmy            1

    you can see that Ian and john is not existing..

    please help and advise if possible show me some sample query..

    thanks in advance..

     

All Replies

  • Wednesday, February 06, 2013 2:04 AM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    Try

    ;with AllInfo as (select * from (select distinct StaffName, StafId from Staff) X cross join (select distinct [date] from TimeTable) Y)

select AI.*, count(TT.CustomerID) as CountCustomer

from AllInfo AI LEFT Join TimeTable TT ON AI.StaffId = TT.StaffId and AI.[Date] = TT.[Date]

GROUP BY AI.StaffID, AI.StaffName, AI.[Date]

    Once you get result in this format it's easy to PIVOT it the way you want although you can also do this in the reporting tool.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog



     
  • Wednesday, February 06, 2013 2:33 AM
     
     
    Sign In to Vote
    0

    Hi Naomi,

    Just want to ask if where did you get the alias 'AA' and also upon running the query that you have given, it seems that the query is asking me to group also all the remaining columns from select AI.*

    Msg 8120, Level 16, State 1, Line 18

    Column 'AllInfo.StaffName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    please advise... thanks in advance again

     
  • Wednesday, February 06, 2013 2:35 AM
    Moderator
     
     
    Sign In to Vote
    0
    I made necessary corrections in my script. My fingers sometimes are slower than my mind and make their own typos.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Wednesday, February 06, 2013 2:49 AM
     
     
    Sign In to Vote
    0

    hahhahaha.. you're you know what 3 words for you. "YOU'RE THE BEST!"

    anyway, just a little bit curious on what do you mean by using PIVOT and a reporting tool? cause i really want a result like

    Date                 Steve      Joey    John     Ian    Jimmy

    2013-01-01       2            1          0          0         1

    2013-01-05       0            0          1          0         1

    2013-01-10       0            0          0          1         0

    or like this without repeating the date

    Date               Staff

    2013-01-01     Steve            2

                           Joey              1

                           John              0

                           Ian                0

                           Jimmy            1

    2013-01-05     Steve             0

                           Joey               0

                           John              1

                           Ian                0

                           Jimmy            1

    2013-01-10    Steve             0

                           Joey              0

                           John              0

                           Ian                1

                           Jimmy            0

    do you have any idea, pardon cause im sure you really have an idea with this one hahaha

     
  • Wednesday, February 06, 2013 2:57 AM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    Damn, I typed the answer, then posted a blog link, decided to re-post it, pressed back on the Browser and lost the response :(

    Ok, I will not repeat the PIVOT query I typed, but this blog post may help to do pivot in SSRS

    Report Builder 3.0 - Table or Matrix Wizard

    If you want to do PIVOT in T-SQL, the syntax is very simple - once you get your results into a table or a query using the query above, you can apply PIVOT on it. In fact, we can transform the query above into PIVOT this way:

    ;with AllInfo as (select * from (select distinct StaffName, StafId from Staff) X cross join (select distinct [date] from TimeTable) Y)

select AI.[Date], 
COUNT(case when AI.StaffName = 'Steve' then TT.CustomerID END) as [Steve],
COUNT(case when AI.StaffName = 'John' then TT.CustomerID END) as [John],

etc.
from AllInfo AI LEFT Join TimeTable TT ON AI.StaffId = TT.StaffId and AI.[Date] = TT.[Date]

GROUP BY AI.[Date]

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


     
  • Wednesday, February 06, 2013 3:15 AM
     
     
    Sign In to Vote
    0

    Hi Naomi,

    Yep I got your point here and this is really great but the thing is What if i have 100 staff so meaning, i should input 100 condition?

    i believe that in terms of execution it is a bad practice, correct me if im wrong but is there any way to attain that result without doing such condition?

     
  • Wednesday, February 06, 2013 6:15 AM
     
      Has Code
    Sign In to Vote
    0

    I have Tested this Query Please Check! 

    SELECT T.[Date],S.ID ,COUNT(S.ID) AS SaleCount
INTO #Report
FROM TimeTable T 
LEFT JOIN StaffTable S ON T.StaffID = S.ID
GROUP BY S.ID,T.[Date]



DECLARE @Date VARCHAR(10)
DECLARE Cur_DistinctDate CURSOR FOR 
    SELECT distinct [Date] FROM TimeTable
        
    OPEN Cur_DistinctDate
    FETCH NEXT FROM Cur_DistinctDate INTO @Date
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        
         --SELECT @Date
         --select ID from StaffTable where ID NOT IN (SELECT ID FROM #Report WHERE [Date]= @Date) 
         
         INSERT INTO #Report
         select @Date AS [Date], ID, 0 AS SaleCount 
				from StaffTable where ID NOT IN (SELECT ID FROM #Report WHERE [Date]= @Date) 
			
        
        FETCH NEXT FROM Cur_DistinctDate INTO @Date
        END

    CLOSE Cur_DistinctDate
    DEALLOCATE Cur_DistinctDate

SELECT R.*,StaffFirstName FROM #Report R
INNER JOIN StaffTable S ON S.ID=R.ID

ORDER BY [Date],ID
DROP TABLE #Report


     
  • Wednesday, February 06, 2013 10:07 AM
     
     
    Sign In to Vote
    0

    sorry bro, can't understand what you've just did. can u explain more further?

    thanks in advance,

     
  • Wednesday, February 06, 2013 10:07 AM
     
     
    Sign In to Vote
    0

    Hi Naomi,

    any other advise?

     
  • Wednesday, February 06, 2013 11:01 AM
     
     
    Sign In to Vote
    0
    Did u try my query.
     
  • Wednesday, February 06, 2013 11:03 AM
     
     
    Sign In to Vote
    0

    Its giving me result u want:

    Date ID SaleCount StaffFirstName
    2013-01-01 1 2 Steve
    2013-01-01 2 2 Joey
    2013-01-01 3 0 John
    2013-01-01 4 0 Ian
    2013-01-01 5 1 Jimmy
    2013-01-05 1 0 Steve
    2013-01-05 2 0 Joey
    2013-01-05 3 1 John
    2013-01-05 4 0 Ian
    2013-01-05 5 1 Jimmy
    2013-01-10 1 0 Steve
    2013-01-10 2 0 Joey
    2013-01-10 3 0 John
    2013-01-10 4 1 Ian
    2013-01-10 5 0 Jimmy

     
  • Wednesday, February 06, 2013 12:44 PM
    Moderator
     
     Answered
    Sign In to Vote
    1
    In this case I would rely on SSRS to do pivoting or alternatively do a dynamic pivot. In this case you first need to get your results into a table (say, temp table) and then do the dynamic pivot - there are plenty of examples online and in this forum itself.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Wednesday, February 06, 2013 12:45 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi Mikel,

    See my answer above. BTW, I sleep at night at least 5-6 hours, so don't be surprised if I didn't answer 2 hours ago.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog