the job of a rollup core is to consolidate multiple core databases into one database. When you create a query on core A, the result will be different if you do the same query on the rollup core’s database because it holds the information from all connected cores. Are you sure that you were connected to the rollup core with your LDMS console, when you execute your query? If so, the results should be the same…
... also, in addition to Christian's explanation (great job!) there's one additional thing to remember.
SQL Studio will run in a "all devices" scope, since it doesn't know about them and just reports on the data. Where as a REPORT will (always) respect scopes, because LANDesk will make sure that those apply (and people can't access / do stuff to devices that they shouldn't be able to see).
That one tends to trip some folks up some times (i.e. - situations where people SHOULD have "all devices" but don't for some reason).
That's usually the 2 possibilities in this sort of situation.
You can always try to find a device that's listed in your SQL report & not your "proper report" (or LD query) and work out from there in a reverse-engineering kinda way why you might not be seeing it (tends to reveal some "duh - didn't think of THAT" type thing usually).
Hope that helps .