TSQL join over 4 tables with exclude condition

Question

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

The problem is, I'm getting an endless loop when I execute my query. It takes a long time to execute and shows a lot of entries several times. I think it's because of the 2nd inner join. Maybe i should use another join type?

Some sample data:

dbo.client

PK_ID | Type

-------------

 1    |  22

 2    |  33

dbo.contact

PK_ID | FK_Client | Surname | Lastname

---------------------------------------

 12   |  1        | Doe     | John

dbo.ad

PK_ID | FK_Client

-----------------

 54   |  1

dbo.table_ad_attribute

FK_ad | FK_attribute

---------------------

 54   |   42

Desired output:

PK_ID | Surname | Firstname

---------------------------

  1   | Doe     | John

Do you know how to solve this issue? Perhaps which join to take in this scenario?

Thanks for your help!

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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.

TSQL join over 4 tables with exclude condition

Sign up