CODEWITHSUNDEEP
mysqlsqlsubquery
user3033933
user3033933

Reputation: 57

SQL: I want to select all columns from table1 where columnA in table1 is equal to ColumnB in table2

I have 2 tables that have a common column Material:

Table1

MaterialGroup | Material | MaterialDescription | Revenue

MG1| DEF| Desc1| 12

MG2| ABC| Desc2| 13

MG3| XYZ| Desc3| 9

MG4| IJK| Desc4| 5


Table2
Vendor | VendorSubgroup| Material| Category

KM1| DPPF| ABC| Cat1

KM2| DPPL| XYZ| Cat2

So, I want to select all records from table1 where Material in table1 matches Material in table2

In the above scenario, I would want this result because the Material "ABC" and "XYZ" are present in table2:

MG2| ABC| Desc2| 13

MG3| XYZ| Desc3| 9

Upvotes: 0

Views: 258

Answers (2)

Linger
Linger

Reputation: 15058

SELECT * FROM Table1 AS t1
WHERE t1.Material IN
(
  SELECT DISTINCT t2.Material 
  FROM Table2 AS t2
)

Upvotes: 2

Santhosh
Santhosh

Reputation: 1791

SELECT T1.*
FROM TABLE1 AS T1
INNER JOIN TABLE2 AS T2
ON T1.MATERIAL = T2.MATERIAL

Upvotes: 1

Related Questions