
USE emcsoft_db ;

-- CUSTOMERS
SELECT customer.id_customer, customer.firstname, customer.lastname, speciality AS job_title, type_ad, zipcode, addr.city, addr.country

FROM customer 
INNER JOIN (SELECT DISTINCT id_customer, MAX(type_ad) AS type_ad, MAX(zipcode) AS zipcode, MAX(city) AS city, MAX(country) AS country FROM address GROUP BY id_customer) AS addr
ON customer.id_customer = addr.id_customer 

WHERE type_ad = 1 
AND addr.country NOT LIKE '' AND addr.city NOT LIKE '' AND addr.zipcode NOT LIKE '' 

ORDER BY id_customer ASC ;



-- ORDERS
-- SELECT * FROM manifestation ORDER BY id_manifestation DESC ;
SELECT id_commande AS id_order, commande.date_submit, id_customer, commande.id_manifestation AS id_congress, original_title AS congress,
MONTANT_TTC AS amount

FROM commande 
INNER JOIN manifestation ON commande.id_manifestation = manifestation.id_manifestation 

WHERE commande.id_manifestation IN (361, 363, 362) AND DUE_TTC = 0 

AND id_customer IN (
SELECT customer.id_customer
FROM customer 
INNER JOIN (SELECT DISTINCT id_customer, MAX(type_ad) AS type_ad, MAX(zipcode) AS zipcode, MAX(city) AS city, MAX(country) AS country FROM address GROUP BY id_customer) AS addr
ON customer.id_customer = addr.id_customer 
WHERE type_ad = 1 
AND addr.country NOT LIKE '' AND addr.city NOT LIKE '' AND addr.zipcode NOT LIKE '' 
ORDER BY id_customer ASC 
) ;








