namitnmt namitnmt - 4 months ago 9
MySQL Question

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

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

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;