J'ai 4 tables
Tableau 1
UserId (PK) ———- UserName
1 —————— ABC
2 ——————- PQR
Tableau 2
CustId (PK) ——— CustName
1 ———————– Cust1
2 ———————– Cust2
3 ———————– Cust3
Tableau 3
CustId (FK) ———- UserId (FK)
1 ———————– 1
2 ———————– 2
Table4
OfficeId (PK) ———- OfficeName ——– CustId (Fk)
1 ———————— Off1 ——————- 1
2 ———————— Off2 ——————- 1
3 ———————— Off3 ——————- 2
Tabl5
OfficeId (FK) ———- UserId
1 ————————- 1
3 ————————- 2
La question est quand l'user est associé à 3 Cust, mais il est assigné des bureaux appartenant à seulement 2 du Cust, alors il devrait returnner l'officeId non atsortingbué? De tables ci-dessus,
Lorsque je passe UserId = 1 à ma procédure stockée
Je veux la sortie suivante
OfficeId ——— OfficeName
2 —————– Off2
Je ne veux pas garder une trace des tables qui sont, donc j'utilise des noms de table qui ont plus de sens (pour moi) …
l'utilisation not exists()
select o.OfficeId, o.OfficeName from users_customers uc inner join office o on uc.CustId = o.CustId where uc.UserId = @UserId and not exists ( select 1 from users_office uo where uo.UserId = @UserId and uo.OfficeId = o.OfficeId )
using except
(cela supprimera également les résultats en double)
select o.OfficeId, o.OfficeName from users_customers uc inner join office o on uc.CustId = o.CustId where uc.UserId = @UserId except select o.OfficeId, o.OfficeName from users_office uo inner join office o on uo.OfficeId = o.OfficeId where uo.UserId = @UserId
SELECT OfficeId, OfficeName FROM Table4 WHERE OfficeId NOT IN ( SELECT Table4.OfficeId FROM Table3 INNER JOIN Table4 ON Table3.CustId = Table4.CustId INNER JOIN Tabl5 ON Tabl5.UserId = Tabl3.UserId AND Tabl5.OfficeId = Table4.OfficeId );