Home / Questions / 350294
Accepted
tripossi
Asked: 2020-04-29 01:59:06 +0800 CST 2020-04-29 01:59:06 +0800 CST

SQL Query in ORACLE

  • 772

I have to make a query and I don't finish finding the correct form.

I have a table in an oracle database. in which has the following values:

example table

numero |  clase   | modelo |
  0701 | especial | alto   |
  0702 | estándar | básico | 
  0701 | estándar | medio  |
  0705 | especial | alto   |

The user will only insert class and model but I want to get those records whose number is repeated both in class and model. I put the query that I have so far in case it is better understood.

Select * FROM ejemplo WHERE ((clase = 'especial' AND modelo = 'alto') OR (clase = 'estándar' AND modelo = 'medio'));

This query will return 3 records, 2 with number 0701 and another with number 0705 but I only want it to return the records with number 0701 (the number matches in the results obtained).

In this second part is where I have the problem and I can't find the solution no matter how hard I search.

Any suggestion?

Thank you

sql

1 Answers

  1. Best Answer

    I don't know your intention, nor if it will work for the rest of the occurrences, but exactly what the answer is asking for is to make a group and keep the ones that are repeated twice.

    SELECT clase, modelo, numero, COUNT(numero) as Cuentas
FROM ejemplo 
WHERE ((clase = 'especial' AND modelo = 'alto') OR (clase = 'estándar' AND modelo = 'medio'))
GROUP BY clase, modelo, numero
HAVING COUNT(numero) = 2;
    • 1