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

Question

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

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

The title may not represent the issue well.

I have a table with information about cars (id (CP), name, model, among others) and another one with the "Extras" (id (CP), car_id (CS) p>

    +---------------------+
    |      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 display the car data (Make, Models) ONLY if the extras I want are related to it, ie 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, it also needs to be presented.

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

What I've tried

I've tried to make a query where one of the conditions was that the record was present in another table, but since the extras are separated by rows, I could not search for all the rows and at the same time only present a record.

In other words, the solutions I tried to exploit do not seem to me efficient or are too complicated and for simple lack of knowledge (experience), I'm not seeing how I can do this.

sql-server query

asked by anonymous 30.06.2017 / 12:15

2 answers

Problems updating data with empty DateTime Close function when clicking outside the element

score 1 · Answer 1

Here's the hint for what you're asking for, which works with any number of accessories you search for.

-- 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 mass 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

That said, I suggest that you create a third table containing the list of accessories, regardless of vehicle make / model. Thus, in the tbl_extras table would only be the vehicle code and the accessory code. It is more reliable to search by code than by text.

score 0 · Answer 2

You can perform the query using the HAVING clause together with a JOIN in the tbl_extras table:

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 query above:

I use INNER JOIN to search for the extras of each vehicle;
In the IN clause I only restrict the extras that I want to be present in the vehicle in question;
The GROUP BY indicates that the result will be counted for every id of vehicle, regardless of the other fields;
The HAVING clause is used when we want to restrict the results with some aggregate function, in this case the COUNT ;
I count the selected records and return only those that have more than 2, ensuring that only vehicles that have at least 2 records in the tbl_extras with the filtered descriptions will be returned.