Better approach to query SQL Server using separate
I have two tables, TableA and TableB, to which TableA.TableA_Id-> 1..n <-TableB.TableA_Id is connected. Simple PK-FK.
I need to retrieve individual TableA records given a certain condition on TableB. Here's my first approach:
SELECT * FROM TableA INNER JOIN TableB B ON A.idA = B.IdA AND B.Date = '2009-01-10' ORDER BY A.Id;
That's good, but it doesn't give me "great" recordings. Some of the records in table B may be duplicated, and therefore I could get the same records more than once.
So I ended up doing a subquery (performance is not an issue, given that there will probably be 20/30 max entries in the subset):
SELECT * FROM TableA WHERE TableA.Id IN (SELECT DISTINCT IdA FROM TableB WHERE Date = '20090110') ORDER BY TableA.IdA;
This works great.
Now the question is, how can I use Inner Join and still get different values? Is this possible in a single pass or is the subquery required? What am I missing?
source to share
I think the normal existing operator is what you need:
SELECT *
FROM TableA A
WHERE Exists( select B.IdA from TableB B where A.IdA = B.IdA and B.Date = '2009-01-10' )
ORDER BY A.Id;
Efficiency is the best approach.
If you need values from another table and to avoid using separate ones, you can join a subquery, which is grouped like this:
Select TableA.*, groupedTableB.OtherData
From TableA
Inner join
(
select TableB.IdA, Sum(TableB.Data) SummaryData
from TableB where TableB.Date = '2009-01-10'
group by TableB.IdA
) groupedTableB on groupedTableB.IdA = TableA.IdA
source to share
And the problem with using
SELECT DISTINCT * FROM TableA A INNER JOIN TableB B ON A.idA = B.IdA AND B.Date = '2009-01-10' ORDER BY A.Id;
this is?
If that means it returns duplicate idA values, it is because you are selecting too many columns, if you cannot decrease what you select, you need a subquery.
source to share