Skip to content
Advertisement

How can I get specific rows in a table by the value of a specific queried column of a different table?

I have the Table1 and the Table2 tables. Table1 has the B (Primary Key) and IC columns. Table2 has the CC, SC and IC columns:

Table Table1

B IC
B1 IC1

Table Table2

CC SC IC
CC1 SC1 IC1
CC1 SC2 IC1
CC2 SC1 IC1
CC2 SC2 IC1

I want to query Table1, filtering its rows by a value of the B column. After finding the row (and I find only one since B is PK), I want to use the corresponding value of the IC column to query for the rows in Table2 that have the same IC value as the one of the IC column obtained from the first step. So, I want to filter these rows by specific CC and SC values.

The above representations explain the concept:

  1. I query Table1 for the rows whose B value is B1.
  2. I get that the corresponding IC value is IC1.
  3. I query Table2 for the rows whose IC value is identical to the previous one, IC1, and I want to filter the found rows by the specific CC2 and SC1 values. So, the desired result, in this case, is the third row of Table2.

They tell me to use views when I can. I know that I can’t pass inputs to a view; moreover, I don’t want to use stored procedures. How can I create a view to get all the Table2 rows whose IC value is obtained from the Table1’s IC column, and filter these rows by specific CC and SC values? Maybe I need to use subqueries instead? Do I need to use joins? Thanks for your help.

Advertisement

Answer

If I understood your question correctly then you can try this

SELECT * FROM table2 WHERE IC = (SELECT IC FROM table1 WHERE B='B1')
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement