MySql: разница двух столбцов, возвращаемых несколькими объединениями

Question

У меня есть БД, которая имеет множество предприятий и отношений между ними. Я пытаюсь найти способ получить все предприятия, которые продает B2, этому B1 тоже не продает. Но только на B2, где B1 и B2 продают одному и тому же бизнесу.

V = Vendor, C = Client, оба являются предприятиями, но их понимание с точки зрения Vendor/Client упрощает объяснение.

V1 -продает-> C1 <- продает- V2 -продает-> C2

Я ищу все C2, которые также не находятся в C1 для конкретной начальной точки V1.

Мои текущие таблицы:

mysql> DESCRIBE business;  
+---------------+-------------------+------+-----+----------+----------------+
| Field         | Type              | Null | Key | Default  | Extra          |
+---------------+-------------------+------+-----+----------+----------------+
| business_id   | int(11)           | NO   | PRI | NULL     | auto_increment |
| email         | varchar(255)      | YES  |     |          |                |
| name          | varchar(255)      | NO   |     | NULL     |                |
| city          | varchar(255)      | YES  |     |          |                |
| state         | varchar(255)      | YES  | MUL |          |                |
| cCount        | int(10) unsigned  | YES  | MUL | 0        |                |
| scCount       | int(10) unsigned  | YES  | MUL | 0        |                |
| vCount        | int(10) unsigned  | YES  | MUL | 0        |                |
| svCount       | int(10) unsigned  | YES  | MUL | 0        |                |
+---------------+-------------------+------+-----+----------+----------------+

mysql> DESCRIBE relation_sells_to;
+---------+----------+------+-----+---------+--------+
| Field   | Type     | Null | Key | Default | Extra  |
+---------+----------+------+-----+---------+--------+
| start   | int(11)  | NO   | MUL | NULL    |        |
| end     | int(11)  | NO   | MUL | NULL    |        |
+---------+----------+------+-----+---------+--------+

mysql> DESCRIBE vcvc;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| vendor  | int(11) | NO   | MUL | NULL    |       |
| client  | int(11) | NO   |     | NULL    |       |
| vendor2 | int(11) | NO   |     | NULL    |       |
| client2 | int(11) | NO   |     | NULL    |       |
+---------+---------+------+-----+---------+-------+

Я могу использовать эти два запроса для получения актуальных результатов, но мне приходится удалять таблицу vcvc каждый раз, когда я хочу обновить ее, и в настоящее время ее сборка занимает 15 минут. Мне также не понадобится доступ ко всей этой информации в любое время. Возможно, мне придется создавать списки тезисов для нескольких тысяч одновременно, но не для полной базы данных.

CREATE TABLE IF NOT EXISTS vcvc (INDEX vendor (vendor), INDEX client2 (client2))
SELECT r.start AS vendor, r.end AS client, r2.start AS vendor2, r3.end AS client2
FROM relation_sells_to AS r
JOIN relation_sells_to as r2 ON r.end = r2.end
JOIN relation_sells_to AS r3 ON r2.start = r3.start
WHERE r.start != r2.start
AND r.end != r3.end;

SELECT DISTINCT(client2), cCount, scCount, vCount, svCount 
FROM vcvc 
JOIN business AS b ON client2=b.business_id 
WHERE vendor = #### 
AND client2 NOT IN (SELECT client FROM vcvc WHERE vendor = ####) 
ORDER BY cCount DESC;

Есть ли способ сделать это в одном запросе, поэтому мне не нужно создавать новую таблицу каждый раз, когда мне нужно получить эту информацию? В настоящее время у меня 500k отношений, и когда я строю эту таблицу, у меня получается более 100 миллионов строк, и большинство из них мне не нужны. В идеале я мог бы взять электронную почту (предпочтительно) или business_id в качестве исходного бизнеса / продавца, а затем просто вернуть список Business / клиентов с именем, городом, штатом, cCount, vCount и сделать так, чтобы они были в порядке DESC по vCount и вероятно LIMIT 20ish. Скорость не является большой проблемой. Я просто хочу получить текущие результаты, не создавая большую таблицу, в которой мне нужно 20 из 100+ миллионов строк. Первоначально я пытался работать с временной таблицей, но потому что я не могу "открыть" временную таблицу так, как мне нужно в данный момент.

Спасибо за помощь.

Обновите данные образца и желаемый результат. Некоторые из cCount и vCounts могут быть отключены, я скопировал реальные данные, затем изменил их, но я не знаю, правильно ли я обновил все числа, добавив необходимые соединения, чтобы показать, что мне нужно.

mysql> SELECT * FROM business;
+-------------+----------------+----------------------+----------------+------------+--------+---------+--------+---------+
| business_id | email          | name                 | city           | state      | cCount | scCount | vCount | svCount |
+-------------+----------------+----------------------+----------------+------------+--------+---------+--------+---------+
|        1    | [e-mail]    | Bobs Construction    | Virginia Beach | Virginia   |     62 |       3 |      0 |       0 |
|        2    | [e-mail]    | Upholstery by Sue    | Austin         | Texas      |     20 |       3 |      4 |       4 |
|        3    | [e-mail]    | Jim & Associates     | Crowley        | Texas      |      5 |       3 |      0 |       0 |
|        4    | [e-mail]    | Jon Jon architects   | Costa Mesa     | California |     67 |       3 |      0 |       0 |
|        5    | [e-mail]    | Joes Pizza           | Hamden         | Conecticut |     7  |       1 |      0 |       0 |
|        6    | [e-mail]    | Tims WIndows         | Miami          | Florida    |     10 |       2 |      0 |       0 |
|        7    | [e-mail]    | Rons Hot Rods        | Costa Mesa     | California |     8  |       4 |      0 |       0 |
+-------------+----------------+----------------------+----------------+------------+--------+---------+--------+---------+

mysql> SELECT start, end FROM relation_sells_to;
+-------+--------+
| start | end    |
+-------+--------+
|  3    | 1      |
|  3    | 2      |
|  2    | 4      |
|  2    | 5      |
|  4    | 2      |
|  1    | 5      |
|  4    | 5      |
|  4    | 6      |
|  4    | 7      |
+-------+--------+

-- Run code to build vcvc table.  Code is above.

mysql> SELECT * FROM vcvc WHERE vendor = 3;
+--------+--------+---------+---------+
| vendor | client | vendor2 | client2 |
+--------+--------+---------+---------+
|   3    | 1      |  2      |    4    |
|   3    | 1      |  2      |    5    |
|   3    | 1      |  4      |    2    |
|   3    | 2      |  1      |    5    |
|   3    | 2      |  4      |    5    |
|   3    | 2      |  4      |    6    |
|   3    | 2      |  4      |    7    |
+--------+--------+---------+---------+

Desired Output:

Select client_id, name, city, state, cCount, vCount FROM MAGIC WHERE email = [e-mail]
+-----------+---------------------+----------------+------------+--------+---------+
| client_id | name                | city           | state      | cCount | vCount  |
+-----------+---------------------+----------------+------------+--------+---------+
|   4       | Jon Jon architects  | Costa Mesa     | California |     67 |       0 |
|   6       | Tims WIndows        | Miami          | Florida    |     10 |       0 |
|   7       | Rons Hot Rods       | Costa Mesa     | California |     8  |       0 |
|   5       | Joes Pizza          | Hamden         | Conecticut |     7  |       0 |
+-----------+---------------------+----------------+------------+--------+---------+
-- Ordered by cCount, business_id 2 is NOT in this list because 3 sells to 2 so I don't care that 4 sells to 2.

ОБНОВЛЕНИЕ 2:

http://sqlfiddle.com/

Вот скрипка этого. Данные в скрипте немного отличаются от данных выше тем, что у них есть еще 2-3 соединения, чтобы правильно показать, что я хочу.