I need to show all categories 1

[Jackxxx]

I need to show all categories, even if the client has no points for some of them. I'm not sure how to adjust my select staement to provide this.

SELECT tblLookupCateg.Category, IsNull(Sum(tblPoints.Points),0) AS TotalPoints, MONTH(tblpoints.[pDate]) pMonth
FROM stblpoints left outer JOIN
tblLookupCateg ON stblpoints.CategID = tblLookupCateg.CategID
WHERE (YEAR(tblpoints.[pDate]) = 2010) AND (MONTH(tblpoints.[Date]) = 8) AND (tblPoints.IndivID=14)
GROUP BY tblLookupCateg.Category,tblpoints.[pDate], MONTH(tblpoints.[pDate])

Category TotalPoints Month
PlanA 0 8
PlanB 3 8
PlanC 0 8
PlanD 5 8

 

[gmmastros]

Change "left outer join" to "right outer join"
Change "WHERE" to "AND"

Do those 2 things and let me know if this works. If it does, and you would like me to explain it, let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jackxxx]

George,

Yes, please tell me more...

 

[gmmastros]

It's late and I'm tired. I'll post an explanation in the morning.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Sorry about the delay.

Take a look at this: thread183-1504081

In my second response in that thread, I show an example of left joins and various ways of constructing a query. Notice how you can make a left join behave as though it were an inner join by putting conditions in the where clause instead of the join clause. This is a very important concept to understand. Once you learn this technique, and truly understand it, you will be able to write complex queries is less time.

If I wasn't in such a hurry last night, I would have posted a sample query for you to try.

SELECT  tblLookupCateg.Category, 
        IsNull(Sum(tblPoints.Points),0) AS TotalPoints, 
        MONTH(tblpoints.[pDate]) pMonth
FROM    tblLookupCateg 
        Left Join tblpoints 
          ON  tblpoints.CategID = tblLookupCateg.CategID
          AND YEAR(tblpoints.[pDate]) = 2010
          AND MONTH(tblpoints.[Date]) = 8 
          AND tblPoints.IndivID=14
GROUP BY tblLookupCateg.Category,MONTH(tblpoints.[pDate])

Notice that my original advice was to change LEFT to RIGHT, but in this example I am still using LEFT join. Of course, to get this to work, I needed to change the order in which the tables are listed. I chose to do it this way because I don't like RIGHT joins. I don't have a single production query that uses a right join. I bet 80% of my joins are inner joins, 19.9% are left joins, and 1 or 2 are cross joins.

Like I said... this is important. If you don't understand any of this, let me know and I will explain more.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jackxxx]

George,

Just woundering if you have any experience with Fusion Chart? My reson for needing the previous select was for a stored procedure that I want to use in s chart. I have posted the code below that I use for the chart and here is the problem. The chart shows each of the categories and compares three months (the user selects the first and last months in the three). What is happening is that when the chart fills the three months categories do not line up in the chart, meaning they should all be shown in the same order, but they don't.



Public Function CreateChart() As String

Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

'Database Objects - Initialization
Dim cmdFC As SqlCommand
Dim dtrFC As SqlDataReader
cmdFC = New SqlCommand
cmdFC.CommandText = "PointsChart"
cmdFC.CommandType = CommandType.StoredProcedure
cmdFC.Connection = New SqlConnection(ConnectStr)
cmdFC.Parameters.AddWithValue("@YearPassed", ddYear.SelectedValue)
cmdFC.Parameters.AddWithValue("@MonthPassed", ddMonth.SelectedValue)
cmdFC.Parameters.AddWithValue("@IndivID", Profile.Individual)
cmdFC.Connection.Open()

'Database Objects - Initialization
Dim cmdFCprev As SqlCommand
Dim dtrFCprev As SqlDataReader
cmdFCprev = New SqlCommand
cmdFCprev.CommandText = "PointsChart"
cmdFCprev.CommandType = CommandType.StoredProcedure
cmdFCprev.Connection = New SqlConnection(ConnectStr)
cmdFCprev.Parameters.Clear()
cmdFCprev.Parameters.AddWithValue("@YearPassed", ddYear.SelectedValue)
cmdFCprev.Parameters.AddWithValue("@MonthPassed", (ddMonth2.SelectedValue + 1))
cmdFCprev.Parameters.AddWithValue("@IndivID", Profile.Individual)
cmdFCprev.Connection.Open()

'Database Objects - Initialization
Dim cmdFCprev2 As SqlCommand
Dim dtrFCprev2 As SqlDataReader
cmdFCprev2 = New SqlCommand
cmdFCprev2.CommandText = "PointsChart"
cmdFCprev2.CommandType = CommandType.StoredProcedure
cmdFCprev2.Connection = New SqlConnection(ConnectStr)
cmdFCprev2.Parameters.Clear()
cmdFCprev2.Parameters.AddWithValue("@YearPassed", ddYear.SelectedValue)
cmdFCprev2.Parameters.AddWithValue("@MonthPassed", (ddMonth2.SelectedValue))
cmdFCprev2.Parameters.AddWithValue("@IndivID", Profile.Individual)
cmdFCprev2.Connection.Open()

Try


Dim strXML As String, strCategories As String, strDataCurr As String, strDataPrev As String, strDataPrev2 As String ', i As Integer

'Initialize <graph> element
strXML = "<graph caption='Monthly Passport Points' numberPrefix='' decimalPrecision='0' rotateNames='1' >"

'Initialize <categories> element - necessary to generate a multi-series chart
strCategories = "<categories>"

'cmdFC.Connection.Open()
dtrFC = cmdFC.ExecuteReader()
cmdFC.Parameters.Clear()

dtrFCprev = cmdFCprev.ExecuteReader()
cmdFCprev.Parameters.Clear()

dtrFCprev2 = cmdFCprev2.ExecuteReader()
cmdFCprev2.Parameters.Clear()


'Initiate <dataset> elements
strDataCurr = "<dataset seriesName='Previous Month' color='AFD8F8'>"
strDataPrev = "<dataset seriesName='Previous Month 2' color='F6BD0F'>"
strDataPrev2 = "<dataset seriesName='Previous Month 3' color='556b2f'>"

Dim arrData As ArrayList = getCategories()
For x = 0 To arrData.Count - 1
strCategories = strCategories & "<category name='" & arrData(x) & "' />"
Next

While dtrFC.Read()
strDataCurr = strDataCurr & "<set value='" & dtrFC.Item("TotalPoints").ToString() & "' />"
End While
While dtrFCprev.Read()
strDataPrev = strDataPrev & "<set value='" & dtrFCprev.Item("TotalPoints").ToString() & "' />"
End While
While dtrFCprev2.Read()
strDataPrev2 = strDataPrev2 & "<set value='" & dtrFCprev2.Item("TotalPoints").ToString() & "' />"
End While

'Close <categories> element
strCategories = strCategories & "</categories>"

'Close <dataset> elements
strDataCurr = strDataCurr & "</dataset>"
strDataPrev = strDataPrev & "</dataset>"
strDataPrev2 = strDataPrev2 & "</dataset>"

'Assemble the entire XML now
strXML = strXML & strCategories & strDataCurr & strDataPrev & strDataPrev2 & "</graph>"

'Create the chart - MS Column 3D Chart with data contained in strXML
Return FusionCharts.RenderChartHTML("../../Charts/FCF_MSColumn3D.swf", "", strXML, "Points", "650", "400", False) ', False)

arrData = Nothing

Catch ex As Exception
Return ex.Message
FC.Text = ex.Message
Finally
cmdFC.Connection.Close()
cmdFCprev.Connection.Close()
cmdFCprev2.Connection.Close()
End Try
End Function

 

[gmmastros]

Just woundering if you have any experience with Fusion Chart?

Sorry. No. I've never even heard of Fusion Chart.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Just to add to George's explanation about the problem - check this blog post

http://beyondrelational.com/blogs/n...-t-bring-all-records-from-the-left-table.aspx

PluralSight Learning Library