Find common value from a column where value from separate column matches 3 different values

Apologies for the poorly worded title. I was not entirely sure how to describe the issue. It's easier illustrated too me.

I have a database table that lists content ids and values. One ID can have different values so id = 1 may have 1 or many rows depending on how many values are associated with it.

I want to find all ids that are associated with 3 different values in this case. Currently, the SQL is written in several subqueries but this causes a great deal of time to resolve if there are a lot of different values being searched for.

example:

Select id from table1 where type = 'report'
and id in (select id from table2 where value=1)
and id in (select id from table2 where value=2)
and id in (select id from table2 where value=3) 
order by id

The content can have many linked tables to do subquery searches on. The more added the more run time which is not ideal. What is the best method to reducing the number of subqueries?

you could use inner join with table2 using different table name alias

Select t1.id 
from table1  t1
where t1.type = 'report'
INNER JOIN table2 t2a ON t1.id = t2a.id  and t2a.value  = 1
INNER JOIN table2 t2b ON t1.id = t2b.id  and t2b.value  = 2
INNER JOIN table2 t2c ON t1.id = t2c.id  and t2c.value  = 3 
order by t1.id

and for better performance you should add a composite index on table2 on column (value, id) and on table table1 on columns ( type, id)

I want to find all ids that are associated with 3 different values in this case. Currently, the SQL is written in several subqueries but this causes a great deal of time to resolve if there are a lot of different values being searched for. example: Select id from table1 where type = 'report' and id in (select id from table2 where value=1) and

You're looking for the 'HAVING' keyword. I may not have implemented it correctly without seeing a data sample, but this ought to give you a direction to look into.

SELECT id 
FROM table1
WHERE type = 'report'
GROUP BY id
HAVING COUNT(id) = 3

Let`s work on a different example where we will use two values from two different columns where the values exist in the same row. The return value will be from the third column and it will also be in the same row of the 2 values that will be given. Here we will be using a formula which contains the INDEX and MATCH Function.

I would suggest writing the query like this:

select t1.id
from table1 t1
where t1.type = 'report' and
      exists (select 1 
              from table2 t2
              where t2.id = t.id and t2.value = 1
             ) and
      exists (select 1 
              from table2 t2
              where t2.id = t.id and t2.value = 2
             ) and
      exists (select 1 
              from table2 t2
              where t2.id = t.id and t2.value = 3
             ) ;
order by t1.id

With indexes on table1(type, id) and table2(id, value) I would expect this to have the best performance.

Vlookup multiple columns and return the corresponding values with INDEX and MATCH functions. Sometimes, you may have a range of data which contains three columns, now you want to lookup on the table to match two criteria values, if both the two values matches, it will return the data from the third column C.

Extract multiple match values into separate rows. If you want to extract multiple matches into the separate rows in excel, just need to use ROWS instead of the COLUMNS function in the above formula as follows: = IFERROR ( INDEX ($D$3:$D$7, SMALL ( IF ($C$3:$C$7="excel", ROW ($C$3:$C$7)- ROW ( INDEX ($C$3:$C$7,1,1))+1), ROWS ($E$2:E2))),"") If you want to extract the second matches, then put the result into the cell E3, you just need to drag the Fill Handler in Cell E2 to E3.

I need a formula to find the maximum value in column 2 based on the value available in the column 1 for ex, I have value "TC001" repeated for 4 times in columnA and the corresponding values in Column B will be as shown below Column A Column B TC001 BASE_1106ST TC001 BASE_1106ST TC001 BASE_1102IMT TC001 BASE_1108ST I need a formula to find the

Col_index_num has value 2, as we want to pull value from the second column of the range. Finally, range_lookup has value 0, because we want to find an exact match of “City” values. Please note that we put absolute cell reference in table range ($ before B3:C7 range) as we must fix our lookup table.

Comments
  • show some data example and expected result
  • This sped things up quite a bit. Thank you.
  • for better performance you should add a composite index on table2 on column (value, id) and on table table1 on columns type, id) .. answer updated .
  • I like the elegance of this answer as well. Thanks!
  • @Floydvoyd . . . I would expect it to have the best performance.