Suppose, we've a table named college. In this table we've the following columns: College ID, College name, State and Established in. Now I want to find the names only of those colleges where the college have another branch in the same state.
Recently I've started learning database and couldn't find the solution of this problem anywhere. Please help me write the query in MySQL with explanation
I tried joining the same tables with alias as c1 and c2 and ran some query having select clause inside another select clause but it didn't happen to work
For example, colleges 'C1' & 'C3' are available at two places in states 'S1' & 'S3' respectively.
DECLARE @College TABLE (Id int identity, Name Varchar(50), State
Varchar(50), Est datetime);
Insert into @College (Name, State, Est)
Values ('C1', 'S1', '01/01/2010'),('C1', 'S1', '01/01/2020'),
('C2', 'S2', '01/01/2010'),
('C3', 'S3', '01/01/2015'),('C3', 'S3', '01/01/2022')
Select Name From @College
Group by Name, State Having Count(*)>1
The above query will return college names 'C1' and 'C3' as below.
Name
C1
C3