Consult customers with their confirmed orders

Situation

We need to prepare a list of confirmed orders. The customer name is in Cliente, while the date and status are in Pedido.

We want to keep only orders with status Confirmado and show the most recent ones first.

Relevant data

Final query

SELECT
    c.IdCliente,
    c.Nombre AS Cliente,
    pe.IdPedido,
    pe.FechaPedido,
    pe.Estado
FROM dbo.Cliente AS c
INNER JOIN dbo.Pedido AS pe
    ON pe.IdCliente = c.IdCliente
WHERE pe.Estado = 'Confirmado'
ORDER BY
    pe.FechaPedido DESC,
    pe.IdPedido DESC;

How it works

INNER JOIN keeps rows that match through IdCliente. A person with several confirmed orders appears several times because the unit of the result—what each row represents—is an order, not a customer.

WHERE is applied after matches are formed and discards the other states. The second ORDER BY criterion resolves date ties and makes the presentation deterministic: the same set keeps the same order.

Expected result

Verified in SSMS

The result contains only the two confirmed orders and preserves the descending order defined by date and identifier.

Variation: orders confirmed within a period

To search March 2026, we use an inclusive lower boundary, which admits its starting value, and the start of the next month as an exclusive upper boundary, which leaves that value out:

WHERE pe.Estado = 'Confirmado'
  AND pe.FechaPedido >= '20260301'
  AND pe.FechaPedido <  '20260401'

This form includes any time on the last day without depending on the precision of the date type.

Limits and cautions

If we only need to know which customers have at least one confirmed order, returning one row per order produces duplicates from the customer’s perspective. In that case EXISTS represents the question better than adding DISTINCT without checking why the rows are repeated.

We also check indexes and volume. An index organizes values to make rows easier to access. A foreign key requires each reference to point to a valid row, but it does not necessarily create every index this query needs.

Related topics

• INNER JOIN
• WHERE
• Subqueries, EXISTS, and NOT EXISTS