crystal report - Create report Not Equal to Column

Question

i have two tables Students & attendance, they have a common field SID where they link

Students

SID | Name | Grade | Age |

01 | A1 | B1 | 8 |

02 | A2 | B2 | 9 |

03 | A3 | B1 | 8 |

04 | A4 | B4 | 10 |

05 | A5 | B1 | 8 |

06 | A6 | B1 | 8 |

Attendance

AID | SID | Present | Date |

001 | 01 | YES | 12-12-16 |

002 | 01 | YES | 13-12-16 |

003 | 02 | YES | 12-12-16 |

004 | 02 | YES | 13-12-16 |

i want to create a report in crystal reports, which will give students which are absent, using the attendance table

I.E where attendance.SiD <> Student.SID

Answer 1

First create view using below query in database:

select * from Students where SID NOT IN (Select DISTINCT(SID) from Attendance);

• Create New form
• Drag N Drop Crested Report component on new form
• On right top side select view created from above query.
• Design form using lable and db field
• That's It.

OR IF YOU CAN NOT CREATE VIEW FOLLOW BELOW STEPS:

• Create New form
• Drag N Drop Crested Report component on new form
• select Student table for query
• Design form using lable and db field
• On Form load use below code

SqlConnection cnn;
          string connectionString = null;
          string sql = null;
          connectionString = "connectring to sql server";
          cnn = new SqlConnection(connectionString);
          cnn.Open();
          sql = "select * from Students where SID NOT IN (Select DISTINCT(SID) from Attendance)";
          SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
          DataSet ds = new DataSet();
          dscmd.Fill(ds, "data");
          objRpt.SetDataSource(ds.Tables[0]);
          crystalReportViewer1.ReportSource = objRpt;
          crystalReportViewer1.Refresh();

Just manually assigned join table query.