qa_posts select query

Question

Can you tell me how fetch data from qa_posts ?i need seelect query for questions And relation answer, commet,etc.

Seem this table have low performance,because a large number of fields that contain null values ​​.

Why this table fields is many null?

Comments

"Seem this table have low performance,because a large number of fields that contain null values" => This doesn't make much sense to me. Firstly, performance is something that affects the queries, and not such a static entity like a table. Secondly, denormalization is a process that is only justified to increase query performance. Otherwise, nobody would denormalize. So it is 100% the opposite to what you're saying

---

+1 to pupi1985. If those values weren't null, what would they be? The tables are fine, not an ideal DB structure, but it works. DB performance also relies on the host machine to be able to cope.

---

I think I would split the table into multiple smaller tables.For exmple "title" field often is empty.

---

That way you wouldn't be able to perform such a simple query like getting a questions with their answers without a join. As I said in my first comment: if it wasn't because of performance then denormalizing wouldn't make any sense at all. You can read more about this if you google for how to solve hierarchies in logical data models, particularly the kill all children strategy, which is the one used in this case

Answer 1

You'll need to run a series of JOIN statements on the qa_posts table, joining it to itself. Use the PostID value for the Question and join answers and comments by using WHERE PostID = ParentID. I may be slightly off on those column headers as I don't have the DB open in front of me, but it'll be pretty obvious what I'm referring to. Be careful pulling the data from the content column as it will contain HTML tags that you'll need to deal with.

Comments

this qurey used for fetch data
    SELECT qa_posts.postid AS qposid,qa_posts.content AS qcontent,
    answer.postid AS aid,
    answer.parentid AS aparentid,
    answer.type AS atype,
    comment.parentid AS cparent,
    comment.type AS ctype,
    comment.content AS ccontent
    FROM qa_posts
    LEFT JOIN qa_posts answer ON qa_posts.postid=answer.parentid
    LEFT JOIN  qa_posts comment ON answer.postid=comment.parentid
    WHERE qa_posts.postid=1
    ORDER BY qa_posts.parentid;

    but the results dose not like the following query

    SELECT * FROM qa_posts WHERE postid=1
    UNION
    SELECT * FROM qa_posts WHERE parentid=1
    UNION
    SELECT * FROM qa_posts WHERE parentid IN (SELECT postid parentid FROM qa_posts WHERE parentid=1)
    ORDER BY parentid;

    The second query results are not sorted in tandem.I need results like this

|  id  | parentid | type  |
+------+----------+-------+
|  1   |     0    |   Q   |
+------+----------+-------+
|  5     |     1    |   C   |
+------+----------+-------+
|  2   |     1    |   A   |
+------+----------+-------+
|  4   |     2    |   C   |
+------+----------+-------+
|  8   |     2    |   C   |
+------+----------+-------+
|  6   |     1    |   A   |
+------+----------+-------+
|  7   |     6    |   C   |
+------+----------+-------+
|  11  |     6    |   C   |
+------+----------+-------+
.