john.1020 john.1020 - 3 years ago 216
SQL Question

TSQL join over 4 tables with exclude condition

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:


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

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!

Answer Source

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

                       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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download