Simple SELECT SQL doubt

Question

Simple SELECT SQL doubt

Navigation

#1 by (3 votes)

0

I have a table with the following records (the fields are Formula - Base):

F1 - B1

F1 - B2

F1 - B3

F2 - B1

F2 - B2

F3 - B1

F3 - B2

F3 - B4

What I want is for me to select which formulas contain the bases B1, B2 and B3.

You have to show me the formulas that contain these three bases or less, as long as the formula does not have a basis other than those three (for example, F3 has B4, so it should not show F3).

I have very basic knowledge of SQL instructions, I would appreciate it if someone could help me out.

Thank you.

sql tablas

asked by FranEET 14.11.2018 в 17:13

source

1 answer

last record entity C # windows form How to pass 2 variables through AJAX to another PHP

score 3 · Accepted Answer

I propose a relatively ANSI solution, although the implementation of the example is clearly SQL Server:

DECLARE @Tabla TABLE ( 
    Formula CHAR(2),
    Base CHAR(2)
)

INSERT INTO @Tabla (Formula, Base)
    SELECT 'F1', 'B1' UNION
    SELECT 'F1', 'B2' UNION
    SELECT 'F1', 'B3' UNION
    SELECT 'F2', 'B1' UNION
    SELECT 'F2', 'B2' UNION
    SELECT 'F3', 'B1' UNION
    SELECT 'F3', 'B2' UNION
    SELECT 'F3', 'B4'

SELECT  DISTINCT Formula 
    FROM @Tabla
    WHERE Base IN ('B1', 'B2', 'B3')
        AND Formula NOT IN (SELECT  DISTINCT Formula 
                    FROM @Tabla
                    WHERE Base not IN ('B1', 'B2', 'B3'))


Formula
-------
F1
F2

Explanation:

We obtain the formulas that have some defined bases
We remove by means of a subquery, those formulas that also have some other base of the defined ones