foo foo - 3 months ago 9
SQL Question

get list of items form DB

In my DB I have two tables:

1. "subjects" table: that include 2 fileds:
*id, *desctiption,
2. "subSubjects" table that include 4 fileds:
*id, *Desctiption, *subject_id, *department_id (primary key of departments table).


I need to return list of Subjects that each subject include list of match SubSubjects inside,
There is a way to do that without using select inside selecet statments?
(join or another statment)?
*I have a class- "Subject" that include Arraylist of subSubjects.

The result in view need to be somthings like this:

*****************************
subject * sub-subject * dep *
*****************************
sub_1 * sub sub1 * 1 *
*********************
* sub sub2 * 2 *
*****************************
sub_2 * sub sub 4 * 1 *
*****************************
sub_3 * sub sub 3 * 1 *
*********************
* sub sub 5 * 2 *
*********************
* sub sub 6 * 2 *
*****************************

Answer

Use the following query:

SELECT
CASE WHEN t.id = (SELECT TOP 1 id -- Sub query
FROM Subsubjects t3
WHERE t3.subject_id = t.subject_id
ORDER BY t3.id) THEN q.description
ELSE ''
END AS Subjects, t.description AS Subsubjects, t.department_id
FROM Subsubjects t
LEFT JOIN subjects q ON q.id = t.subject_id
ORDER BY t.id

And it'll return the expected data as follows:

*****************************
  subject * sub-subject * dep *
  *****************************   
  sub_1   * sub sub1    * 1   *
    *********************
          * sub sub2    * 2   *
  *****************************
  sub_2   * sub sub 4   * 1   *
  *****************************
  sub_3   * sub sub 3   * 1   *
    *********************
          * sub sub 5   * 2   *
    *********************
          * sub sub 6   * 2   *
*****************************

The following is an example how we deal list object with sql queries:

public List<Product> GetAllProducts() //GetAllProducts() is a list-type method
{
    Query = "SELECT * FROM Products";

    Command = new SqlCommand(Query, Connection);

    Connection.Open();

    Reader = Command.ExecuteReader();

    List<Product> products = new List<Product>(); //Created a list

    while (Reader.Read())
    {
        Product product = new Product(); //Created an object from the class
        product.ProductId = Convert.ToInt32(Reader["ProductID"]);
        product.CategoryId = Convert.ToInt32(Reader["CategoryID"]);
        product.ProductName = Reader["ProductName"].ToString();
        product.Details = Reader["Details"].ToString();
        product.Price = (double)Reader["Price"];
        product.Stock = Convert.ToDouble(Reader["Stock"]);

        products.Add(product); //Finally bind the object with the list
    }

    Reader.Close();
    Connection.Close();

    return products;
}
Comments