MySql query - find whats missing in db

Question

I'm having trouble coming up with a query to determine what records are missing from my mysql database.

I don't know how to loop through to find whats missing from a structured listing.

I have a database used as a locations map for an online game I play, field structure is as follows:

Region | Province | City | PlayerInfo

Region/province/city are all numeric values. Region values are 1-30 and Province values are 1-500. These are fixed. My database is to list all the cities a player might have. A given province could have up to 20 cities, or none. When there are no cities in a province, I create a record in the db with blank city and playerinfo values.

For example:

R | P | C | PI
1 | 1 | 3 | Name1
1 | 1 | 7 | Name2
1 | 2 | 4 | Name3
1 | 3 |   |
1 | 4 | 1 | Name2
1 | 4 | 4 | Name4
1 | 6 | 3 | Name5
etc

What I'm trying to do is find what region/province combinations are missing. In the sample data here, I would want to know R:1 P:5.

Is there a way to do this? Let me know if I can provide any more information

Do you mean R:1 P:3 ? I don't see a value of 5 anywhere in your sample data. — Paul Sasik
– Paul Sasik, Commented May 18, 2011 at 18:53
@Paul: I think that's the point. OP is looking for the combinations of 30 Regions x 500 Provinces that do not exist in this table. — Joe Stefanelli
– Joe Stefanelli, Commented May 18, 2011 at 18:57
Exactly, Joe, blank data is fine, I just want to know what I don't have (ie 1:5) — Matt V
– Matt V, Commented May 18, 2011 at 19:00
Although a pure MySql solution could be written (probably need to write a temporary table for that), you may find that moving out to a richer programming language will drastically simplify the solution. Are you ok with that, and if so, what would be the language you'd like to utilize. — John Green
– John Green, Commented May 18, 2011 at 19:09

Dalen · Accepted Answer · 2011-05-18 21:22:08Z

1

Assuming you have 2 tables (province and region) containing data about province and region (if not it is easy to create them with just numeric values as stated on the question):

SELECT comb.reg AS R,comb.pro AS P FROM
   your_table_above t
RIGHT JOIN
(
   SELECT region.id AS reg, province.id AS pro
   FROM region
   JOIN province
   ORDER BY region.id
) AS comb
ON t.R = comb.reg AND t.P = comb.pro
WHERE t.R IS NULL AND t.P IS NULL

this query should give you back all combinations which are not in the table you drawn above.

EDIT

this is how looks the query after the comments below:

SELECT comb.reg AS R,comb.pro AS P FROM
 loc_table t
RIGHT JOIN
(
   SELECT region.id AS reg, province.id AS pro
   FROM region
   JOIN province
   ORDER BY region.id
) AS comb
ON t.Region = comb.reg AND t.Province = comb.pro
WHERE t.Region IS NULL AND t.Province IS NULL

edited May 18, 2011 at 21:22

answered May 18, 2011 at 19:14

Dalen

9,0264 gold badges49 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Matt V Over a year ago

thanks for that. i created the new tables for the possible values of reg/prov, with region.id going from 1-30 and province.id ranging from 1-500.I then ran your code, with only one change: "SELECT Region AS R, Province AS P FROM loc_table t" and I got an error: "MySQL said: #1054 - Unknown column 't.R' in 'where clause' "

Dalen Over a year ago

you have also to change t.R to t.Region and t.P to t.Province on WHERE clause, i just found that i did a typo on my answer, now the query should by correct

Matt V Over a year ago

I made the changes, on the ON and WHERE lines to have the full Region and Province names, but got no records back. I know for a fact theres no records with 15/471 so I'm confused. I checked and there is a region.id of 15 and province.id of 471 .. any thoughts?

Dalen Over a year ago

could you please paste you query here: pastie.org and copy here the link so that i can take a look

Dalen Over a year ago

ok i made a mistake: you have to select the rows from the 'combinationts' (comb) table not from the loc_table. I've edited my answer: SELECT comb.reg AS R,comb.pro AS P FROM . sorry for that.

|

Collectives™ on Stack Overflow

MySql query - find whats missing in db

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related