SQL advanced -> Subquery Examples

Our task is to find clients that lives in cities that was mailed in last mailing campaign. We have clients table and table Mailing1 that stores Mailing list.

SELECT *
FROM Clients
WHERE city IN
(
  select city from Mailing1 group by city
);


But if you need to find all clients that participated in this mailing, you can use Correlated Subquery. Correlation will be maid by cardcode value:
SELECT a.*
FROM clients AS a
WHERE a.cardcode =
(
  select b.cardcode
  from Mailing1 as b
  where a.cardcode = b.cardcode
);

The result can be like this:
CardCodeCardNameCityAddress
101304John StuartHaasbenOlsing St. 125
............


sqlexamples.info