Re: [libreoffice-users] LibréOffice Base Forms, PostgreSQL (JDBC) and Table Joins

[N.B.: Switching to in-line reply format to eliminate the cruft and
because the question stands on its own.]

On Sun, 23 Feb 2014 16:42:44
+0000 Alex Kempshall <alexkempshall@btinternet.com> wrote:

> Jim
>
> You stated that you could generate a query to do what you require.
> Have you based your form on the query?
[big snip]

Thanks for the follow-up, Alex.  (Another "Alex."  I'm sensing a
trend, here ;).)

If I understand you correctly: Yes.  In fact: I made it far simpler
than queries I can easily do ad hoc with the PostgreSQL command-line
interface (psql).

For example: Here's a VIEW (a VIEW, in Postgresql, is a query that's
saved, such that it looks like a table) that employs several LEFT
JOINs, the last three of which are simply to "get to" the hostname(s)
associated with a hardware asset:

 SELECT a.atag, h.hostname, a.owner, s.descr AS status, d.mfgr,
 d.model, a.comments FROM hdwr_assets a
   LEFT JOIN hdwr_status s ON s.status_id = a.status_id
   LEFT JOIN asset_descr d ON d.descr_id = a.descr_id
   LEFT JOIN mac_addrs m ON m.atag = a.atag
   LEFT JOIN ip_addrs i ON i.mac_addr = m.mac_addr
   LEFT JOIN hostnames h ON h.ip_addr::inet = i.ip_addr::inet;

Why the indirection/abstraction?  Because there can be multiple MAC
addrs/asset, multiple IPs/MAC, and multiple hostnames/IP.

*That* might be a bit much to expect of a graphical query/form design
tool.  (Tho it sure would be nice if it was handled.)

All I'm trying to do, so far, is "a single level of indirection," as
it were.  (That is also in that query.  You can see it in the
's.descr as "status" and "ON status_id" bits.)

Regards,
Jim
-- 
Note: My mail server employs *very* aggressive anti-spam
filtering.  If you reply to this email and your email is
rejected, please accept my apologies and let me know via my
web form at <http://jimsun.LinxNet.com/contact/scform.php>.

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted