Requête SQL pour les commandes : clients différents mais ayant la mêmes adresses

FORUM Connecteur Sage Prestashop BtoB et BtoCRequête SQL pour les commandes : clients différents mais ayant la mêmes adresses
Maxence Haurat demandée il y a 2 ans

Pour rechercher dans SQL quelles commandes ont les mêmes adresses de livraison ou de facturation, mais des clients différents, cette requête peut être utilisée :

SELECT DISTINCT(O1.id_order) AS 'ID commande',
O1.reference AS 'référence',
O1.id_customer AS 'id client',
C.lastname AS 'nom client',
C.firstname AS 'prénom client',
C.email AS 'mail client',
O1.id_address_delivery AS 'Id adresse de livraison',
AD.alias AS 'Alias adresse de livraison',
O1.id_address_invoice AS 'Id adresse de facturation',
AI.alias AS 'Alias adresse de facturation'
FROM ps_orders AS O1
JOIN ps_orders AS O2
ON O1.id_order != O2.id_order
AND O1.id_customer != O2.id_customer
AND (O1.id_address_delivery = O2.id_address_delivery
OR O1.id_address_invoice = O2.id_address_invoice)
JOIN ps_address AS AD ON AD.id_address = O1.id_address_delivery
JOIN ps_address AS AI ON AI.id_address = O1.id_address_invoice
JOIN ps_customer AS C ON O1.id_customer = C.id_customer
ORDER BY AD.id_address, AI.id_address