SQL: Select all rows of the accounts which have certain value in a particular column

Question

I have some data in the table1 in this form:

ID1     ID2     Col3   Col4 ...
x1      a1       a  
x1      a1       b  
x1      a2       c  
x2      a1       a
x2      a2       b
x2      a2       c
x3      a3       a
x3      a3       d   
x4      a3       d

I want to display all rows of the accounts(ID1+ID2) which have 'a' in the Col3 field. So output should look like

ID1     ID2     Col3   Col4 ...
x1      a1       a  
x1      a1       b    
x2      a1       a
x3      a3       a
x3      a3       d

This is the query that I am using:

SELECT * 
FROM t1 table1
WHERE EXISTS
(
   SELECT t2.ID1, t2.ID2
   FROM t2 table1
   where t1.ID1 = t2.ID1
   AND t1.ID2 = t2.ID2
   AND t2.Col3 = 'a'
)

This query is taking a lot of time. Is there a faster way?

Thanks!!

---

I found one solution: The table on which I am working has millions of rows. Therefore the query was taking a lot of time. I was able to increase the speed by performing indexing of primary keys on both table.

Thanks!! :)

Answer 1

Solution 1

Select from column 1 and matching row from column 2 but Only if table 1 contains the right data in col3

SELECT ID1, ID2, Col3 FROM table1 LEFT JOIN table2 ON Table1.col3 = "a";

Solution 2

Select from column 1 and matching row from column 2 but Only if table 1 and table 2 contains the right data in col3

SELECT ID1, ID2, Col3 FROM table1 LEFT JOIN table2 ON Table1.col3 = "a" AND Table2.col3 = a;