select rows of multiple tables with special condition and sum (something like: show if)

Question

I try to figure out the following scenario within a single MySQL statement (used in a PHP script):

There is a shop database with public articles, which are shown to everyone (customer 0). Some customers get for some articles special prices (discounts). The last option is that customer A is only allowed to see the stocked amount in stock 1, Customer B is allowed to see articles amount in Stock 1 and 2.

My problem is that I want to display the article overview for customers with the public articles and also special customer articles. If customer A has special conditions for Article 10000, so the public article should be not displayed and only the special customer ones.

The following result is wanted for customer 500:

article_no article_name length width customer_article_no price total_sum
------------------------------------------------------------------------
10000      Article One  10     10    123                 13.50        45
20000      Article Two  15     13                         1.25        10
30000      Article Three 25    25    456                 25.00        35

After some tries I got the following result (X is the not wanted row):

article_no article_name length width customer_article_no price total_sum
------------------------------------------------------------------------
10000      Article One  10     10                        13.50        45    <- X
10000      Article One  10     10    123                 13.00        45
20000      Article Two  15     13                         1.25        10
30000      Article Three 25    25    456                 25.00        35

My database structure is like this:

pastebin

CREATE TABLE IF NOT EXISTS `article` (
  `article_no` int(11) NOT NULL,
  `article_name` varchar(255) COLLATE utf8_bin NOT NULL,
  `length` double NOT NULL,
  `width` double NOT NULL,
  PRIMARY KEY (`article_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `article` (`article_no`, `article_name`, `length`, `width`) VALUES
(10000, 'Article One', 10, 10),
(20000, 'Article Two', 15, 13),
(30000, 'Article Three', 25, 25);

CREATE TABLE IF NOT EXISTS `article_to_customer` (
  `article_no` int(11) NOT NULL,
  `customer_no` int(11) NOT NULL,
  `customer_article_no` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `price` double DEFAULT NULL,
  `public_article` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`article_no`,`customer_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `article_to_customer` (`article_no`, `customer_no`, `customer_article_no`, `price`, `public_article`) VALUES
(10000, 1, NULL, 13.5, 1),
(10000, 500, '123', 13, 0),
(20000, 1, NULL, 1.25, 1),
(30000, 500, '456', 25, 0);

CREATE TABLE IF NOT EXISTS `customer` (
  `customer_no` int(11) NOT NULL,
  `customer_name` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`customer_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


INSERT INTO `customer` (`customer_no`, `customer_name`) VALUES
(1, 'PUBLIC'),
(500, 'CustomerNo1'),
(1001, 'CustomerNo2');


CREATE TABLE IF NOT EXISTS `customer_to_stock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_no` int(11) NOT NULL,
  `stock_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `stock` (
  `stock_id` int(11) NOT NULL AUTO_INCREMENT,
  `stock_name` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`stock_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3 ;


INSERT INTO `stock` (`stock_id`, `stock_name`) VALUES
(1, 'Germany'),
(2, 'Poland');


CREATE TABLE IF NOT EXISTS `stocked` (
  `stock_id` int(11) NOT NULL,
  `article_no` int(11) NOT NULL,
  `stocked` int(11) NOT NULL,
  PRIMARY KEY (`stock_id`,`article_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


INSERT INTO `stocked` (`stock_id`, `article_no`, `stocked`) VALUES
(1, 10000, 12),
(1, 20000, 10),
(1, 30000, 0),
(2, 10000, 33),
(2, 20000, 0),
(2, 30000, 35);

I tried several joins like:

SELECT a.article_no, a.article_name, a.length, a.width,
atc.customer_article_no, atc.price,
(SELECT SUM(sa.stocked) FROM stocked AS sa WHERE (sa.stock_id = 1 OR sa.stock_id = 2) AND sa.article_no = atc.article_no GROUP BY article_no) AS total_sum
FROM article AS a
JOIN article_to_customer AS atc
ON atc.article_no = a.article_no AND (atc.customer_no = 500 OR atc.customer_no = 0)

SELECT a.article_no, a.article_name, a.length, a.width,
atc.customer_article_no, atc.price,
(SELECT SUM(sa.stocked) FROM stocked AS sa WHERE (sa.stock_id = 1 OR sa.stock_id = 2) AND sa.article_no = atc.article_no GROUP BY article_no) AS total_sum
FROM article AS a
JOIN article_to_customer AS atc
ON atc.article_no = a.article_no AND (atc.customer_no = 500 OR atc.customer_no = 0)
GROUP BY atc.public_article

but i am not able to get the wanted result.

So the condition in a quick overview: Select all articles written in the article_to_customer table and sum the stocked amount. If there is a special customer condition (like own customer number or different price), show only the customer article version and ignore the public article.

Please keep in mind: The database structure is just a minimal example without foreign keys and many data. The structure can be changed if needed.

Answer 1

To should split this kind of best-match join into two joins:

FROM article AS a
LEFT JOIN article_to_customer AS atc1
ON atc1.article_no = a.article_no 
AND atc1.customer_no = 500
LEFT JOIN article_to_customer AS atc2
ON atc2.article_no = a.article_no 
AND atc2.public_article = 1

Then you have to add a COALESCE for each column from act in the SELECT list:

COALESCE(atc1.customer_article_no, atc2.customer_article_no),
COALESCE(atc1.price, atc2.price)

If you got articles which don't exist in article_to_customeryou might have to add another condition to get the same result as the Inner Join:

WHERE (atc1.article_no IS NOT NULL OR atc2.article_no IS NOT NULL)