-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.32-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win64
-- HeidiSQL Version:             12.6.0.6765
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Dumping structure for view 100ad.adreses
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `adreses` (
	`address_id` INT(10) UNSIGNED NOT NULL COMMENT 'Adreses ID',
	`full_address` VARCHAR(152) NULL COLLATE 'utf8mb4_unicode_ci'
) ENGINE=MyISAM;

-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `adreses`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `adreses` AS SELECT 
    d7.id AS address_id,
    TRIM(BOTH ', ' FROM 
        REPLACE(
            REPLACE(
                CONCAT(
                    IF(d7.name IS NULL OR d7.name = '' OR d7.name = d6.name, '', d7.name),
                    IF(d7.name IS NOT NULL AND d7.name != '' AND d6.name IS NOT NULL AND d6.name != '' AND d7.name != d6.name, ', ', ''),
                    IF(d6.name IS NULL OR d6.name = '' OR d6.name = d5.name, '', d6.name),
                    IF(d6.name IS NOT NULL AND d6.name != '' AND d5.name IS NOT NULL AND d5.name != '' AND d6.name != d5.name, ', ', ''),
                    IF(d5.name IS NULL OR d5.name = '' OR d5.name = d4.name, '', d5.name),
                    IF(d5.name IS NOT NULL AND d5.name != '' AND d4.name IS NOT NULL AND d4.name != '' AND d5.name != d4.name, ', ', ''),
                    IF(d4.name IS NULL OR d4.name = '' OR d4.name = d3.name, '', d4.name),
                    IF(d4.name IS NOT NULL AND d4.name != '' AND d3.name IS NOT NULL AND d3.name != '' AND d4.name != d3.name, ', ', ''),
                    IF(d3.name IS NULL OR d3.name = '' OR d3.name = d2.name, '', d3.name),
                    IF(d3.name IS NOT NULL AND d3.name != '' AND d2.name IS NOT NULL AND d2.name != '' AND d3.name != d2.name, ', ', ''),
                    IF(d2.name IS NULL OR d2.name = '' OR d2.name = d1.name, '', d2.name),
                    IF(d2.name IS NOT NULL AND d2.name != '' AND d1.name IS NOT NULL AND d1.name != '' AND d2.name != d1.name, ', ', ''),
                    IF(d1.name IS NULL OR d1.name = '', '', d1.name)
                ), ', ,', ','
            ), ', ,', ','
        )
    ) AS full_address
FROM martins_address_name d1
RIGHT JOIN martins_address_name d2 ON d1.id = d2.id_address_name
RIGHT JOIN martins_address_name d3 ON d2.id = d3.id_address_name
RIGHT JOIN martins_address_name d4 ON d3.id = d4.id_address_name
RIGHT JOIN martins_address_name d5 ON d4.id = d5.id_address_name
RIGHT JOIN martins_address_name d6 ON d5.id = d6.id_address_name
RIGHT JOIN martins_address_name d7 ON d6.id = d7.id_address_name ;

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;