Joining data in tables is probably a bit subjective, and there's going to be more than one way to solve it.
I personally only use LEFT joins. It just works for the way I think. I look at table relationships from left to right, where my tables on the left have foreign keys to primary keys in my tables on the right.
Example:
My sql statement would then be:
run sql select d.name as devicename, d.description, n.dnorpattern, p.name as partition from devicenumplanmap as m left join device as d on m.fkdevice = d.pkid left join numplan as n on m.fknumplan = n.pkid left join routepartition as p on n.fkroutepartition = p.pkid
And now I can just keep adding tables on to the right side. For example, if I wanted to know the device pool name.
Table devicepool's pkid is referenced by table device's fkdevicepool column, and since I already have device in the list of tables, from the left, this would work just fine by amending my sql statement as such:
run sql select d.name as devicename, d.description, dp,name as devicepool, n.dnorpattern, p.name as partition from devicenumplanmap as m left join device as d on m.fkdevice = d.pkid left join numplan as n on m.fknumplan = n.pkid left join routepartition as p on n.fkroutepartition = p.pkid left join devicepool as dp on d.fkdevicepool = dp.pkid
Performance aside, you could keep doing this forever.
One trick I learned over the years is that if you have two settings on the web page, which are of the same type (E.g., Your Directory number page has several Calling Search Spaces to set for all of the forwarding options), you have to join the same table again and again, but aliased differently each time.
Example:
run sql select n.dnorpattern, p.name as partition, \ d.cfavoicemailenabled as cfa_vm, d.cfadestination, css_cfa.name as css_cfa, css_scfa.name as css_scfa \ from numplan as n \ left join routepartition as p on n.fkroutepartition = p.pkid \ left join callforwarddynamic as d on n.pkid = d.fknumplan \ left join callingsearchspace as css_cfa on d.fkcallingsearchspace_cfa = css_cfa.pkid \ left join callingsearchspace as css_scfa on d.fkcallingsearchspace_scfa = css_scfa.pkid
This isn't a very common thing I have to do when query the database, but if you're thinking about performing a bulk export of data, then you'll for sure need to know this.
So, what you may want to do is, export a phone on the new system, to get the column headers, and then write a SQL query that can export all of those columns. If there is a column you do not want to pull data on (leave blank) or if there's one you want to statically assign, you can use this trick:
run sql select col1, col2, "", "t", col5 from mytable
This will produce an output where col3 is always blank, and col4 is always 't'. If you don't do this, that's fine, you'll have to then just manipulate the data in Excel as you move it around.
I hope this helps.
