How to display data in crystal report without duplicating the data?

Question

I have a problem in displaying the data in crystal reports using left join because its duplicating my data even though I am using GROUP BY in SQL command.

Here is the list of my tables:

• table 1: complaint

  

• table 2: errordesc (error description)

  

Here is my code in Crystal Reports:

    Dim objConn As MySqlConnection
    Dim daT1, daT2 As MySqlDataAdapter
    Dim activecomp As DataSet
    Dim strConnection As String
    Dim strSQL As String

    strConnection = "server=localhost;user id=root;password=;database=ticketing_system;"
    objConn = New MySqlConnection(strConnection)
    objConn.Open()

    strSQL = "SELECT * FROM errordesc LEFT JOIN complaint ON errordesc.tran_no=complaint.tran_no WHERE errordesc.status='On-process' group by errordesc.err_id "
    daT1 = New MySqlDataAdapter(strSQL, objConn)
    activecomp = New DataSet
    daT1.Fill(activecomp, "comp")
    daT1.Fill(activecomp, "active")

    Dim rpt As New CrystalReport1
    rpt.SetDataSource(activecomp)
    CrystalReportViewer1.ReportSource = rpt

    objConn.Close()

and my output was this:

It duplicated my errordesc.err_id even though I grouped it. :( please help if anyone knows. Thanks in advance...

Answer 1

oohh. i solved my own problem by coding my to crystal reports like this:

Dim objConn As MySqlConnection
    Dim daT1, daT2 As MySqlDataAdapter
    Dim activecomp As DataSet
    Dim strConnection As String
    Dim strSQL As String

    strConnection = "server=localhost;user id=root;password=;database=ticketing_system;"
    objConn = New MySqlConnection(strConnection)
    objConn.Open()


    strSQL = "SELECT * FROM errordesc WHERE errordesc.status='On-process' group by errordesc.err_id "
    daT1 = New MySqlDataAdapter(strSQL, objConn)
    activecomp = New DataSet
    daT1.Fill(activecomp, "errordesc")

    strSQL = "SELECT * FROM complaint left join errordesc on complaint.tran_no=errordesc.tran_no group by complaint.tran_no"
    daT2 = New MySqlDataAdapter(strSQL, objConn)
    daT2.Fill(activecomp, "complaint")

    Dim rpt As New CrystalReport1
    rpt.SetDataSource(activecomp)
    CrystalReportViewer1.ReportSource = rpt

    objConn.Close()

Answer 2

i think that the simple way to remove your duplicate rows is changing your strSQL:

strSQL = "SELECT DISTINCT * FROM errordesc LEFT JOIN "+
    "complaint ON errordesc.tran_no=complaint.tran_no "+
    "WHERE errordesc.status='On-process' group by errordesc.err_id "