I have a DB that has a bunch of businesses and the relationships between them. I am trying to find a way to get all Businesses that B2 sells to that B1 does not sell too. But Only on B2 where B1 and B2 sell to the same business.
V= Vendor, C = Client, Both are businesses but thinking in terms of Vendor/Client makes this easier to explain.
V1 -sells to-> C1 <- sells to- V2 -sells to-> C2
I am looking for all C2 that are not also in C1 for a specific V1 starting point.
My current tables:
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 | |
+---------+---------+------+-----+---------+-------+
I can use these two queries to get up to date results, But I have to drop the vcvc table every time I want to update it and it currently takes 15 min to build it. I also will not need access to all that info at any one time. I may need to generate theses lists for a few thousand at a time but not the full DB.
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;
Is there a way to do this in a single query so I dont have to build a whole new table every time I need to get this information? I currently have 500k relations and when I build this table I end up with over 100 million rows and most of those I don't need. Ideally I would be able to take an email(preferred) or business_id as the starting business/vendor, then just return a list of Business/clients with name, city, state, cCount, vCount and have them order in DESC order by vCount and probably LIMIT 20ish. Speed isn't a huge issue I really just want to be able to get current results without having to build a large table of which I need 20 of the 100+millions rows on it. I originally tried working with a temp table but because I cant "reopen" the temp table like my current query needs.
Thanks for the help.
Update with sample data and desired output. Some of the cCount and vCounts might be off, I copied real data then modified it but I don't know if I updated all the numbers correctly as I added the required connections to show what I need.
mysql> SELECT * FROM business;
+-------------+----------------+----------------------+----------------+------------+--------+---------+--------+---------+
| business_id | email | name | city | state | cCount | scCount | vCount | svCount |
+-------------+----------------+----------------------+----------------+------------+--------+---------+--------+---------+
| 1 | bob@bob.com | Bobs Construction | Virginia Beach | Virginia | 62 | 3 | 0 | 0 |
| 2 | sue@bob.com | Upholstery by Sue | Austin | Texas | 20 | 3 | 4 | 4 |
| 3 | jim@bob.com | Jim & Associates | Crowley | Texas | 5 | 3 | 0 | 0 |
| 4 | jon@bob.com | Jon Jon architects | Costa Mesa | California | 67 | 3 | 0 | 0 |
| 5 | joe@bob.com | Joes Pizza | Hamden | Conecticut | 7 | 1 | 0 | 0 |
| 6 | tim@bob.com | Tims WIndows | Miami | Florida | 10 | 2 | 0 | 0 |
| 7 | ron@bob.com | 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 = jim@bob.com
+-----------+---------------------+----------------+------------+--------+---------+
| 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.
UPDATE 2:
http://sqlfiddle.com/#!9/fdcec/2
Here is a fiddle of it. The data in the fiddle is slightly different than the data above in that it has 2-3 more connections to properly show what I want.
