I'm currently working on a query on SQL Server 2014. I try to do a join over 4 tables with an exclude condition in the table [table_ad_attribute].[FK_attribute] = 42.
The tables are always connected, PK_ID = FK_Tablename :
SELECT [A].PK_ID, [B].Surname, [B].Firstname
FROM [dbo].[client] [A]
INNER JOIN [dbo].[contact] [B]
ON [B].FK_Client = [A].PK_ID
AND [A].Type = 22
// i assume this join is wrong
INNER JOIN [dbo].[ad] [C]
ON [C].FK_Client = [A].PK_ID
INNER JOIN [dbo].[table_ad_attribute] [D]
ON [D].FK_ad = [C].PK_ID
AND [D].FK_attribute = 42
PK_ID | Type
-------------
1 | 22
2 | 33
PK_ID | FK_Client | Surname | Lastname
---------------------------------------
12 | 1 | Doe | John
PK_ID | FK_Client
-----------------
54 | 1
FK_ad | FK_attribute
---------------------
54 | 42
PK_ID | Surname | Firstname
---------------------------
1 | Doe | John
You're joining [A] -> [B] to get your result columns.
You're also joining [A] -> [C] -> [D] to get your filter.
I think what's happening is that your filter is returning multiple instances of each of your [A].PK_ID values so you get that number of [A] [B] rows returned for each [A].PK_ID value.
Assuming I'm right I'd do something like this,
SELECT [A].PK_ID, [B].Surname, [B].Firstname
FROM [dbo].[client] [A]
INNER JOIN [dbo].[contact] [B]
ON [B].FK_Client = [A].PK_ID
AND [A].Type = 22
WHERE [A].PK_ID IN (SELECT DISTINCT [C].FK_Client
FROM [dbo].[ad] [C]
INNER JOIN [dbo].[table_ad_attribute] [D]
ON [D].FK_ad = [C].PK_ID
AND [D].FK_attribute = 42)
Your [C] -> [D] join identifies the [A].PK_ID values you're interested in so I've put that into a subquery which I then use in a WHERE clause for your [A] -> [B] join.