Okay
  Public Ticket #1581208
Query With 'UNION ALL' clause not working
Closed

Comments

  • escueladireccion started the conversation

    Hello,


    I need to generate a table with 'UNION ALL'. This is the query (it is larger but I paste just two sentences), it works perfectly in MySQL and I get resutls, but when I use it in the adminstrator area, I get a blank page and nothing happens.


    I think it is a bug or this plugin does not support 'UNION' clause (the sentences work separately, but they don't work together with 'UNION' clause).

    Could you review this issue?


    Thanks in advance.


    (Select 
    'Ultimos 30' as Rango,
    COUNT(SA.ID),
    SUM(SA.is_correct),
    QC.name
    from wp_watupro_question QU, wp_watupro_qcats QC, wp_watupro_student_answers SA
    Join (
     select SA2.ID from wp_watupro_student_answers SA2, wp_watupro_question QU2, wp_watupro_qcats QC2
     Where SA2.question_id = QU2.ID
     And QU2.cat_id = QC2.ID
     And QC2.ID = 48
    And SA2.user_id = 1
     LIMIT 30 OFFSET 0
     ) JT ON SA.ID = JT.ID
    Where SA.question_id = QU.ID
    And QU.cat_id = QC.ID
    Group by QC.name)
     UNION ALL
    (Select 
    'Ultimos 30' as Rango,
    COUNT(SA.ID),
    SUM(SA.is_correct),
    QC.name
    from wp_watupro_question QU, wp_watupro_qcats QC, wp_watupro_student_answers SA
    Join (
     select SA2.ID from wp_watupro_student_answers SA2, wp_watupro_question QU2, wp_watupro_qcats QC2
     Where SA2.question_id = QU2.ID
     And QU2.cat_id = QC2.ID
     And QC2.ID = 49
    And SA2.user_id = 1
     LIMIT 30 OFFSET 0
     ) JT ON SA.ID = JT.ID
    Where SA.question_id = QU.ID
    And QU.cat_id = QC.ID
    Group by QC.name)


  •  1,667
    Miloš replied

    Hi escueladireccion,
    Thank you for your purchase.

    When you have a complex queries like this our suggestion is to make a VIEW in phpmyadmin or any other database management system and see if the query is returning the right results. If the results are good you can use that query as an input ( SELECT * FROM VIEW_NAME) when creating tables with Add from data source option.


    Best regards.

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | InstagramFront-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | InstagramAmelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables