combine and remove value from database

Question

I have Question_Master and Assessment_Master table. In question_Master i have questions parent-child relationship. When i answer the parent question, When i filter the answered button filter i need to display parent along with child. When i answer the child question, When i filter the answered button filter i need to display parent along with child. But my issue is, when i answer the both question i'm getting child duplicate.

when answer the parent question it get parent-child:

  SELECT a.QM_ID,a.QM_QCM_ID,
       a.QM_Question,a.QM_Type,
       a.QM_Parent_Id,
       c.AM_Answer, 
       c.AM_Comments 
  FROM question_master a 
       INNER JOIN Assessment_master c 
             ON (c.AM_QM_ID = a.QM_ID 
                 AND c.AM_HNM_ID = %d 
                 AND c.AM_HM_ID = %d 
                 AND c.AM_ASM_Local_Id = %@) 
 WHERE a.QM_Parent_Id = 0 
       AND a.QM_Status = 'A' 
       AND a.QM_QCM_ID = %@ 
       AND a.QM_QRM_Id = %@

UNION
SELECT b.QM_ID,
       b.QM_QCM_ID,
       b.QM_Question,
       b.QM_Type,
       b.QM_Parent_Id,
       null, 
       null 
  FROM question_master b 
       INNER JOIN Assessment_master d 
             ON (d. AM_QM_ID = b.QM_Parent_Id 
                 AND d.AM_HNM_ID = %d 
                 AND d.AM_HM_ID = %d 
                 AND d.AM_ASM_Local_Id = %@) 
 WHERE b.QM_Parent_Id != 0 
       AND b.QM_Status = 'A' 
       AND b.QM_QCM_ID = %@ 
       AND b.QM_QRM_Id = %@

when i answer the child question it get parent-child:

    SELECT a.QM_ID,a.QM_QCM_ID,
       a.QM_Question,a.QM_Type,
       a.QM_Parent_Id,
       c.AM_Answer, 
       c.AM_Comments 
  FROM question_master a 
       INNER JOIN Assessment_master c 
             ON (c.AM_QM_ID = a.QM_ID 
                 AND c.AM_HNM_ID = %d 
                 AND c.AM_HM_ID = %d 
                 AND c.AM_ASM_Local_Id = %@) 
 WHERE a.QM_Parent_Id = 0 
       AND a.QM_Status = 'A' 
       AND a.QM_QCM_ID = %@ 
       AND a.QM_QRM_Id = %@

UNION
SELECT b.QM_ID,
       b.QM_QCM_ID,
       b.QM_Question,
       b.QM_Type,
       b.QM_Parent_Id,
       d.AM_Answer, 
       d.AM_Comments 
  FROM question_master b 
       INNER JOIN Assessment_master d 
             ON (d. AM_QM_ID = b.QM_ID 
                 AND d.AM_HNM_ID = %d 
                 AND d.AM_HM_ID = %d 
                 AND d.AM_ASM_Local_Id = %@) 
 WHERE b.QM_Parent_Id IN (SELECT QM_ID 
                            FROM question_master 
                           WHERE QM_Parent_Id = 0 
                                 AND QM_Status = 'A' 
                                 AND QM_QCM_ID = %@ 
                                 AND QM_QRM_Id = %@)

I used combined query from above:

 SELECT a.QM_ID,a.QM_QCM_ID,
       a.QM_Question,a.QM_Type,
       a.QM_Parent_Id,
       c.AM_Answer, 
       c.AM_Comments 
  FROM question_master a 
       INNER JOIN Assessment_master c 
             ON (c.AM_QM_ID = a.QM_ID 
                 AND c.AM_HNM_ID = %d 
                 AND c.AM_HM_ID = %d 
                 AND c.AM_ASM_Local_Id = %@) 
 WHERE a.QM_Parent_Id = 0 
       AND a.QM_Status = 'A' 
       AND a.QM_QCM_ID = %@ 
       AND a.QM_QRM_Id = %@

UNION
SELECT b.QM_ID,
       b.QM_QCM_ID,
       b.QM_Question,
       b.QM_Type,
       b.QM_Parent_Id,
       null, 
       null 
  FROM question_master b 
       INNER JOIN Assessment_master d 
             ON (d. AM_QM_ID = b.QM_Parent_Id 
                 AND d.AM_HNM_ID = %d 
                 AND d.AM_HM_ID = %d 
                 AND d.AM_ASM_Local_Id = %@) 
 WHERE b.QM_Parent_Id != 0 
       AND b.QM_Status = 'A' 
       AND b.QM_QCM_ID = %@ 
       AND b.QM_QRM_Id = %@

UNION
SELECT b.QM_ID,
       b.QM_QCM_ID,
       b.QM_Question,
       b.QM_Type,
       b.QM_Parent_Id,
       d.AM_Answer, 
       d.AM_Comments 
  FROM question_master b 
       INNER JOIN Assessment_master d 
             ON (d. AM_QM_ID = b.QM_ID 
                 AND d.AM_HNM_ID = %d 
                 AND d.AM_HM_ID = %d 
                 AND d.AM_ASM_Local_Id = %@) 
 WHERE b.QM_Parent_Id IN (SELECT QM_ID 
                            FROM question_master 
                           WHERE QM_Parent_Id = 0 
                                 AND QM_Status = 'A' 
                                 AND QM_QCM_ID = %@ 
                                 AND QM_QRM_Id = %@)

But here i'm getting extra child duplicate value. When i use LEFT it getting all the child question, if i use EXCEPT then answer the parent question it don't getting parent-child relation.

Question_Master:

QM_ID    QM_QRM_ID   QM_LCM_ID  QM_QCM_ID   QM_Question       QM_Parent_Id

432         5            19       1         question_parent       0
433         5            19       1         question_child        432
434         5            19       1           question_child1      432

Assessment_Master:

AM_ID     AM_UM_ID   AM_ASM_Local_Id  AM_QM_ID   AM_Answer   AM_Comments  AM_HNM_ID
1          8            1                433       NULL        testing      1

If i answer the child question 433 and i'm getting only parent with 433 child. I'm not getting 434 question.

a.QM_ID  a.QM_QCM_ID  a.QM_Question     a.QM_Parent_Id c.AM_Answer  c.AM_Comments
         432       1          question_parent     0                Null        NULL
          433      1          question_child       432             null        value

Do the %d %@ come from Objective-C wherefore the iOS thing would significant? And what do they mean? The same value each or some sequence of values? — albe, Feb 25 '14 at 15:30
I would need a few things: What db engine are you using (PostgreSQL)? as @Ziouas points out this seems to be a hierarchical thing and that is important. Also what parameters you are passing and a small sample of related data. — albe, Feb 25 '14 at 17:11
@albe: check my edited code with table. I answered child question. It saved to Assessment_Master table. If i filter the query it won't getting parent-child. I need to display all the question. The QM_ID 432,433,434. Because these are all parent-child realtioship. — user3351727, Feb 25 '14 at 18:28
Is this a SQLite database? If so what version? `file (db file name).db` from command line gives this. What is filling in the parameters %d and %@. why %@ when the values look numeric? — albe, Feb 25 '14 at 19:36

score 0 · Answer 1 · edited May 23 '17 at 11:50

0

Which database engine are you using?

Have you tried recurrence to create a simple parent-child relationship?

If ORACLE then take a look at CONNECT BY PRIOR,

if MS SQL then here is equivalent: Simulation of CONNECT BY PRIOR of ORACLE in SQL SERVER.

You won't have to use any union and the results will be much easier to filter and work with.

edited May 23 '17 at 11:50

Community

1
1

answered Feb 25 '14 at 15:15

Ziouas

519
1
6
18

This is for objective-c. Above query is for iOS development. I fetched all the data from server and stored in local database. Then i'm writing query for parent-child. But i'm getting problem. – user3351727 Feb 25 '14 at 17:09
check my edited code with table. I answered child question. It saved to Assessment_Master table. If i filter the query it won't getting parent-child. I need to display all the question. The QM_ID 432,433,434. Because these are all parent-child realtioship – user3351727 Feb 25 '14 at 18:40
Objective-c ok, but which database engine? Have you tried recursive query? – Ziouas Feb 26 '14 at 11:28
Client used SOAP webservie for server data. I'm fetching using TBXML parser library – user3351727 Feb 26 '14 at 11:31

albe · Answer 2 · 2014-02-26T19:07:06.757

0

I am assuming the following:

you are using SQLite or MySQL
the last line in question master has qm_id 434 not 433 (otherwise I don't see a primary key)
you want to display everything in one row and children don't have children of their own

Then this gives what you asked for in your comment (The QM_ID 432,433,434):

SELECT a.QM_ID,GROUP_CONCAT(b.QM_ID) 
FROM question_master a JOIN 
     question_master b ON a.QM_ID = b.QM_PARENT_ID
GROUP BY a.QM_ID;

Add whatever you need from the assessment_master. If you have a deeper hierarchy, this is again different on different platforms. If you want them on separate lines, that is simple.

edited Feb 26 '14 at 19:07

answered Feb 26 '14 at 18:00

albe

551
4
15

If i run your query it will getting all the questions. I don't want this. If i answer the 433 question. I need to get only parent_id 432 child. I don't want to show all other. – user3351727 Feb 27 '14 at 09:17
If i answer the child question 433 and i'm getting only parent with 433 child. I'm not getting 434 question. check edited code and final combine table. – user3351727 Feb 27 '14 at 13:03

combine and remove value from database

2 Answers2