Select, by quantity, items that match conditions

Question

Select, by quantity, items that match conditions

Navigation

#1 by (5 votes)

3

I'm having a question when setting up a query.

I'll set up a fictional scenario to demonstrate.

I have the quantidadeQuestoes table with the fields:

cod_level
cod_category
cod_dificulty
quantity

And I have the questoes table with the fields:

cod_level
cod_category
cod_dificulty
description

My doubt is. How to get from the% wrapper% table the amount specified in the% wrapper% field of the wrapper% wrapper where the other fields are matched.

With my current query I'm picking up all the items and not just the amount specified in the Quantity field, which is what I need:

SELECT
    qt.descricao
FROM
    quantidadeQuestoes qq
INNER JOIN
    questoes qt ON
    qq.cod_nivel = qt.cod_nivel AND
    qq.cod_categoria = qt.cod_categoria AND
    qq.cod_dificuldade = qt.cod_dificuldade

It is for me to put together a test that contains so many questions of a level + category + difficulty.

sql sql-server

asked by anonymous 11.08.2015 / 19:33

1 answer

DER Student Question Topic How to create a recursive anonymous function (Closure)?

score 5 · Accepted Answer

If I understand correctly, you will need a subquery. I got a solution using ROW_NUMBER , which numbers the rows found. The PARTITION BY is being used to zero the count for each row in the quantidadeQuestoes table. It looks like this:

SELECT descricao
FROM (
  SELECT 
    ROW_NUMBER() OVER(PARTITION BY q.cod_nivel, q.cod_categoria, q.cod_dificuldade ORDER BY q.cod_nivel DESC) AS num,
    q.[cod_nivel], 
    q.[cod_categoria], 
    q.[cod_dificuldade], 
    q.[descricao],
    qq.[quantidade]
  FROM questoes q
    INNER JOIN quantidadeQuestoes qq
    ON qq.cod_nivel = q.cod_nivel AND
    qq.cod_categoria = q.cod_categoria AND
    qq.cod_dificuldade = q.cod_dificuldade 
) sq
WHERE sq.num <= sq.quantidade

See demo in sqlfiddle .