KURN KURN - 6 months ago 6
PHP Question

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

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

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)
Comments