1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| --Filtre sur les articles n'ayant qu'un fournisseur
SELECT a.idArticle, a.idUser, u.nom + ' ' + u.prenom AS user, a.article, a.idCatArt, ca.categorieArticle, a.description,a.motCle,cast(@admin as bit) AS visible
FROM sites AS s
INNER JOIN users AS u ON a.idUser = u.idUser
INNER JOIN categorieArticles ca on a.categorieArticle= ca.categorieArticle
WHERE a.idArticle in (
SELECT idArticle FROM articles_fournisseurs
GROUP BY idArticle
HAVING COUNT(*) = 1
AND MAX(CASE WHEN idFournisseurs = 1 THEN 1 END) = 1)
ORDER BY user ASC;
--Filtre sur les articles ayant deu fournisseurs uniquement
SELECT a.idArticle, a.idUser, u.nom + ' ' + u.prenom AS user, a.article, a.idCatArt, ca.categorieArticle, a.description,a.motCle,cast(@admin as bit) AS visible
FROM sites AS s
INNER JOIN users AS u ON a.idUser = u.idUser
INNER JOIN categorieArticles ca on a.categorieArticle= ca.categorieArticle
WHERE a.idArticle in (
SELECT idArticle FROM articles_fournisseurs
GROUP BY idArticle
SUM(CASE WHEN idFournisseurs IN (1,3) THEN 1 ELSE - 1 END) = 2
ORDER BY user ASC; |
Partager