I Know My Keys Are Around Here Somewhere…
Whether you’re just starting out with SQL queries or have been building them from scratch before the K1000 was a twinkle in someone’s eye, one thing you’ve probably had to come to grips with is constructing JOIN statements. (And for those who have not yet begun that journey, JOIN statements simply allow data from more than one database table to be harvested.)
As you may know, the main trick to writing a JOIN statement is figuring out which columns to use – and therein lies the rub. As there’s no current master diagram showing all of the relationships a table’s “master column” (typically called the primary key) has with other tables (typically to columns with the same data, called foreign keys), a lot of time can be wasted trying to figure out this part… especially when tables aren’t directly related at all!
While it remains true that there’s no updated master diagram, the good news is that there’s a new KACE user-reviewed blog on ITNinja with SQL queries which will help you map out these table relationships:
K1000 Reports – K1000 Database Keys Relationship Matrix Queries
For example, want to know which tables relate to the MACHINE table and how? Just run the example filtered query, and there they are! Want to do the same for other tables? Simply change a couple of values and run the report!
And as usual, if you have any questions or need more examples, just post in the comments section – the blog’s author (jverbosk) is more than happy to help. So, have fun on the journey ahead and be sure to share your finds so that we can all benefit! ^_^