locked
SQL Query problem RRS feed

  • Question

  • User-305388250 posted

    Hey,

    I have the followg web method..

        [WebMethod(Description = "Method displays forums.")]
        public DataSet Display_Forums()
        {
            DataSet ds = new DataSet();
            string database = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|/forum.mdb;Persist Security Info=True";

            const string connStr = "Provider=Microsoft.Jet.OleDb.4.0; " + "Data Source=|DataDirectory|forum.mdb;";
            OleDbConnection dbConn = new OleDbConnection(connStr);

            string queryStr = "SELECT forum_id, (forum_name + info + moderator) AS Forum, ('?') AS Threads, ('?') AS Posts, ('?') AS Recently_Posted FROM forums";

            OleDbConnection myConn = new OleDbConnection(database);
            OleDbCommand dbCommand = new OleDbCommand(queryStr, myConn);
            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(queryStr, myConn);

            myConn.Open();
            myDataAdapter.Fill(ds, "forums");
            myConn.Close();
            return ds;
        }

    But i need to incorparate the following two SQL queries into it to fill in the (?) fields in the above SQL statement..

    "Select COUNT(threads.thread_id) AS no_of_threads FROM threads WHERE threads.forum_id = '" + id + "'";

    "SELECT members.username, members.user_id, posts.forum_id, posts.date_posted " +
                "FROM members, posts WHERE posts.forum_id = '" + id + "' ORDER BY posts.date_posted DESC"

    But i dont know how to do it?

    Would i be able to put the three SQL queries and make one..?

    Any Ideas what i can do?

     

    Thanks, 

    Regards

    Tuesday, May 12, 2009 8:07 PM

Answers

  • User-501974848 posted

    You can combine the SQL queries as one statement. for example:

    SELECT forum_id, (select count(*) from forums) AS total, (select count(*) from forums f where ff.forum_id = f.forum_id) AS test
    FROM Forums AS ff;

    the  above query will give you forum id, total posts, matching post with forum_id

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 12, 2009 10:15 PM

All replies

  • User-501974848 posted

    You can combine the SQL queries as one statement. for example:

    SELECT forum_id, (select count(*) from forums) AS total, (select count(*) from forums f where ff.forum_id = f.forum_id) AS test
    FROM Forums AS ff;

    the  above query will give you forum id, total posts, matching post with forum_id

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 12, 2009 10:15 PM
  • User-305388250 posted

    Hey,

    I have used your SQL query and changed it slightly to get out the forum name, but what i also want to get out is the number of threads AS Threads, in the names forum, the number of of posts if there are any threads AS Posts, and the date of the last post AS Last_Posted..

    I have managed to do this with the two SQL statements below

    "Select COUNT(threads.thread_id) AS no_of_threads FROM threads WHERE threads.forum_id = '" + id + "'";

    "SELECT members.username, members.user_id, posts.forum_id, posts.date_posted " +
                "FROM members, posts WHERE posts.forum_id = '" + id + "' ORDER BY posts.date_posted DESC"

    but in the same way you have suggested combining the SQL into one, would i be able to combine the 2 above into one?

    At the moment the SQL query you have suggested and with some changes it is giving me the following:-

    Test Area 6 ? 1
    General Forum 6 ? 1
    FAQ 6 ? 1
    Questions & Support 6 ? 1
    Suggestions 6 ? 1
    Bug Report 6 ? 1

    So where you can see 6 i want that to be the number of threads in the forum, the ? to be the posts, and the 1 to be the date of last posted..

    Any help would be much appreciated.

    Thanks

    Regards

    Wednesday, May 13, 2009 4:49 AM
  • User854688209 posted

    I would suggest you to use stored procedure instead of calling the query from code. Stored procedure is secure, easy to maintain.

    In stored procedure it can be achieve like this

    DECLARE @count INT 

    Select @count = COUNT(threads.thread_id) AS no_of_threads FROM threads WHERE threads.forum_id = @id --@id will be passed as parameter

    SELECT @count, members.username, members.user_id, posts.forum_id, posts.date_posted " +
                "FROM members, posts WHERE posts.forum_id = @id ORDER BY posts.date_posted DESC

     

    Refer below links to know how to call stored procedure from code:

    http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx

    http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

    http://www.codeguru.com/columns/vb/article.php/c8671

     

    Wednesday, May 13, 2009 5:02 AM