I have two tables both containing various companies. I want to select everything from tableOne
that appears in tableTwo
(in fact, only if it appears in tableTwo
). I’ll do this by comparing their companyID
fields. They must also have the same value for a column someYear
. So in other words, I want to return the companies that appear in tableTwo
from tableOne
, if and only if their someYear
columns match.
tableOne
companyID someYear
--------- --------
1 2010
2 2009
3 2011
1 2011
7 2009
tableTwo
companyID someYear
--------- --------
1 2010
2 2008
3 2011
4 2011
5 2009
I want to return 1
and 3
(company 2
’s year differs so I don’t want it). My useless attempt:
SELECT one.*
FROM tableOne one, tableTwo two
WHERE one.[companyID] in (
SELECT DISTINCT companyID
FROM tableTwo
)
and one.someYear = two.someYear;
The problem here is that it returns millions of rows, when both tables contain less than 10,000 entries. I can't recreate the output exactly because of sensitive information. The table/column names here are all made up as I'm sure you can tell. Also I should probably point out that neither table appears to have a primary key. I assume that causes them to have nothing to match on. I’m using SQL Server 2008. Any help would be hugely appreciated.
Try this
select one.*
from tableOne as one
inner join tableTwo as two
on one.companyID = two.companyID and
one.someYear = two.someYear
INNER JOIN
is not the only way of performing a semi-join. Here's another equally valid way of doing the same:
SELECT *
FROM tableOne one
WHERE EXISTS (
SELECT *
FROM tableTwo two
WHERE one.companyID = two.companyID
AND one.someYear = two.someYear
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With