How to get Mysql Select Query? single question multiple answers

Question

Table =A [qa_request]

 sno | userno | name | uquestion | subject
-----+--------+------+-----------+---------
 1 | xxxxx | yyy | q1 | s1

Table =B [qa_answer]

 qa_id | answer | a_subject
-------+--------+-----------------
 1 | a1 | adminsubject-1
 1 | a2 | adminsubject-2
 1 | a3 | adminsubject-3

MY SQL Query :

select distinct
 a.sno, a.userno, a.name, a.uquestion, a.subject,
 b.answer, b.a_subject
 from qa_request as a 
 inner join qa_answer as b ON a.sno=b.qa_id 

Sample Output:

 sno | userno | name | uquestion | subject | answer | a_subject
-----+--------+------+-----------+---------+--------+----------------
 1 | xxxxx | yyy | q1 | s1 | a1 | adminsubject-1
 | | | | | a2 | adminsubject-2
 | | | | | a3 | adminsubject-3

In other words, I want to show the actual values from the question only on the first line with the first answer, and leave them blank on the following lines, with the second (etc.) answer(s).

How do I do this?

• Tom V: So you want empty fields in sno userno etc? Why? SQL will return a joined dataset for matching records from both tables. Why can't you do it in the presentation layer or what is the reasoning behind this?
• Rick James: Formatting issues are better done in the application.

Answer 1

As noted by Tom V, this can easily be done in the presentation layer, instead of in the query itself. Trying to do it in the query means that the query will take longer to run, and in most cases in a web application (which I assume is what you're working with here), you can have several webservers all interacting with one database server - offloading tasks from the DB server to the web servers will frequently make sense.

However, it's also true that you'd (probably) be sending more data from the database server to the web server, and there's a cost in that bandwidth as well. And, of course, smaller websites might have just one webserver, in which case it might actually make sense to use more resources on the DB server.

So, the following query should give you what you want. We use a variable in the query to check if the current row has the same sno as the previous row; if it doesn't, we don't show the columns from qa_request. The process creates an extra column (aliased to dummy); the main query is put into a subquery simply so we can leave that dummy column out.

CREATE TABLE qa_request (
 sno int,
 userno varchar(20),
 name varchar(20),
 uquestion varchar(5),
 subject varchar(20)
 );
INSERT INTO qa_request
VALUES (1,'xxxxx','yyy','q1','s1')
 ,(2,'zzzzz','qqq','q7','s8')
;
CREATE TABLE qa_answer (
 qa_id int,
 answer varchar(5),
 a_subject varchar(20)
 );
INSERT INTO qa_answer
VALUES (1,'a1','adminsubject-1')
 ,(1,'a2','adminsubject-2')
 ,(1,'a3','adminsubject-3')
 ,(2,'a37','adminsubject_37')
;
SELECT sno
 ,userno
 ,name
 ,uquestion
 ,subject
 ,answer
 ,a_subject
 FROM (SELECT CASE WHEN @last_sno <> a.sno THEN CONCAT('',a.sno) ELSE '' END as sno
 ,CASE WHEN @last_sno <> a.sno THEN a.userno ELSE '' END as userno
 ,CASE WHEN @last_sno <> a.sno THEN a.name ELSE '' END as name
 ,CASE WHEN @last_sno <> a.sno THEN a.uquestion ELSE '' END as uquestion
 ,CASE WHEN @last_sno <> a.sno THEN a.subject ELSE '' END as subject
 ,b.answer
 ,b.a_subject
 ,@last_sno := sno as dummy
 from qa_request as a 
 inner join qa_answer as b ON a.sno=b.qa_id 
 CROSS JOIN (SELECT @last_sno := -1) init
 ORDER BY a.sno, b.answer
 ) sq
;

db-fiddle here