Query that only returns the result if a field is in another table

Question

The title may not represent the question well.

I have a table with information about cars (id(CP), name, model, among others) and another one with the "Extras" (id(CP), id_carro(CS), name_do_extra) that the cars have.

    +---------------------+
    |      tbl_carros     |
    +---------------------+
    | ID | Marca | Modelo |
    +----+-------+--------+
    | 22 | VW    | Golf   |
    +----+-------+--------+
    | 23 | Smart | ForTwo |
    +----+-------+--------+
    | 34 | BMW   | 740d   |
    +----+-------+--------+

+------------------------------+
|          tbl_extras          |
+------------------------------+
| ID | id_car | Extra          |
+----+--------+----------------+
| 1  | 22     | Airbag         |
+----+--------+----------------+
| 2  | 22     | ABS            |
+----+--------+----------------+
| 3  | 22     | Cruise Control |
+----+--------+----------------+
| 4  | 24     | ABS            |
+----+--------+----------------+
| 5  | 24     | Airbag         |
+----+--------+----------------+

Above are the two tables in question.

What I needed was a way to present the car data(Make, Models) ONLY if the extras I want are related to it, that is, when I order all cars with ABS and Cruise Control, the only result, in this case, is VW Golf, in a single Row. If there is more than one car with the same extras, I also needed it to be introduced.

I hope I made myself clear. I can always answer any more questions you may have.

What I’ve already tried

I tried to do a query where one of the conditions was the record to be present in another table, but since the extras are separated by Rows, I could not search for all lines and at the same time, present only one record.

That is, the solutions I tried to explore do not seem efficient or are too complicated and for simple lack of knowledge (experience), I do not see how I can do this.

Answer 1

Here is the suggestion for what you request, which works with any number of accessories to search.

-- código #1
-- informe os extras a pesquisar
CREATE TABLE #Pesquisa (Extra varchar(30) unique);
INSERT into #Pesquisa values
  ('ABS'),
  ('Cruise Control');

-- calcula número de itens extras a pesquisar
declare @Qtd int;
set @Qtd= (SELECT count(*) from #Pesquisa);

-- monta lista de extras em uma variável
declare @Acessórios varchar(200);
set @Acessórios= '';
SELECT @Acessórios+= (Extra + ', ') from #Pesquisa;
set @Acessórios= left(@Acessórios, (len(@Acessórios) -1));

--
with ctePesqExtra as (
SELECT id_car
  from tbl_extras as T1
       inner join #Pesquisa as T2 on T1.Extra = T2.Extra
  group by id_car
  having count(*) = @Qtd
)
SELECT C.*, @Acessórios as [Acessórios]
  from tbl_carros as C
       inner join ctePesqExtra as E on C.ID = E.id_car;

---

Code to generate the mass of data for testing:

-- código #2
set nocount on;

CREATE TABLE tbl_carros (
  ID int not null primary key,
  Marca varchar(20) not null,
  Modelo varchar(30) not null
);
INSERT into tbl_carros values
   (22, 'VW', 'Golf'),
   (23, 'Smart', 'ForTwo'),
   (24, 'BMW', '740d');

CREATE TABLE tbl_extras (
  id int identity,
  id_car int references tbl_carros(ID),
  Extra varchar(30)
);
CREATE clustered INDEX I1_tbl_extras on tbl_extras (id_car);

INSERT into tbl_extras (id_car, Extra) values
   (22, 'Airbag'),
   (22, 'ABS'),   
   (22, 'Cruise Control'),
   (24, 'ABS'),
   (24, 'Airbag');
go

---

Having said that, I suggest that you create a third table containing the list of accessories, independent of make/model of vehicle. In this way, the tbl_extras table would only contain the vehicle code and the accessory code. It is more reliable to search by code than by text.

Answer 2

You can perform the query using the clause HAVING along with a JOIN on the table tbl_extras:

SELECT tc.id
  FROM tbl_carros tc
       INNER JOIN tbl_extras te ON te.id_car = tc.id_car
 WHERE te.extra IN ('ABS', 'Cruise Control')
 GROUP BY tc.id
HAVING COUNT(1) >= 2

Explaining the query above:

• I use the INNER JOIN to get the extras from each vehicle;

• In the clause IN I restrict only the extras that I wish to be present in the vehicle in question;

• The GROUP BY indicates that the result will be counted for each id vehicle, independently of other fields;

• The clause HAVING is used when we want to restrict the results with some aggregation function, in this case the COUNT;

• Count the selected records and return only those who have more than 2, ensuring that will be returned only vehicles that have at least 2 records in tbl_extras with the filtered descriptions.