Why is this query returning unwanted results?

Question

Good morning,

I have a problem with this query:

SELECT  
    P.txt_nome AS Pergunta,   
    IP.nome AS Resposta,   
    COUNT(*) AS Qtd   
FROM  
    tb_resposta_formulario RF   
    INNER JOIN formularios F ON  
        F.id_formulario = RF.id_formulario   
    INNER JOIN tb_pergunta P ON  
        P.id_pergunta = RF.id_pergunta   
    INNER JOIN tb_resposta_formulario_combo RFC ON  
        RFC.id_resposta_formulario = RF.id_resposta_formulario   
    INNER JOIN itens_perguntas IP ON  
        IP.id_item_pergunta = RFC.id_item_pergunta   
WHERE  
    RF.id_formulario = 2   
GROUP BY    
    P.txt_nome,   
    IP.nome

This is the actual result of this query:

|Pergunta| Resposta |Qtd|
|Produto |Combo 1MB | 3 |
|Produto |Combo 2MB | 5 |
|Produto |Combo 4MB | 1 |
|Produto |Combo 6MB | 1 |
|Produto |Combo 8MB | 4 |
|Região |MG | 3 |
|Região |PR | 2 |
|Região |RJ | 3 |
|Região |SC | 1 |
|Região |SP | 5 |

These are the results I was expecting:

|Produto | Região |Qtd|
|Combo 1MB | MG | 3 |
|Combo 2MB | SP | 5 |
|Combo 4MB | SC | 1 |
|Combo 6MB | RJ | 1 |
|Combo 8MB | PR | 2 |

I am using the PIVOT and UNPIVOT operators but the result is not satisfactory.

Has anyone already faced this situation before? Do you have any insight you can offer?

I already analyzed these links:

• http://stackoverflow.com/questions/213702/sql-server-2005-pivot-on-unknown-number-of-columns
• http://stackoverflow.com/questions/152770/transpose-a-set-of-rows-as-columns-in-sql-server-2000
• http://stackoverflow.com/questions/428041/sql-server-2005-turn-columns-into-rows
• http://stackoverflow.com/questions/159456/pivot-table-and-concatenate-columns-sql-problem#159803
• http://stackoverflow.com/questions/198716/pivot-in-sql-2005#199763

Att, Pelegrini

Answer 1

The "obvious" answer is: because the query is incorrect. We really know nothing about the table structure and what you're trying to achieve.

Concerning at least one very basic problem in your query: you're expecting the columns |Produto | Região |Qtd| in your response, yet the query unambiguously selects the columns Pergunta, Reposta and Qtd, which coincides with the result you're getting.

How well are you acquainted with SQL at all? It may be worth it to read an introductory text. I'd suggest this as a good introduction. (Uses Oracle, but the principles are the same)

Answer 2

I would also recommend having a look at Jeff Atwood's explanation of JOINS.