basic many-to-many sql select query

Question

I think this should be easy, but it's evading me. I've got a many-to-many relationship between Accounts and Account Groups. An Account can be in zero or more Groups, so I'm using the standard join table.

Accounts 
--------
ID
BankName
AcctNumber
Balance

AccountGroups
-------------
ID
GroupName

JoinAccountsGroups
------------------
AID
GID

I'm using MS Access, FWIW. Also, this is for a low-bandwidth situation, so code optimization isn't as important as simplicity/readability.

I'm using php as a presentation layer, so a bare-bones result from Access is fine.

As for what to do with the multi-result situation, I actually have two things I'm trying to build. The first lists all the groups in one column thus:

Bank       AcctNum       Balance    Groups
--------|--------------|----------|----------------
Citi       930938        400        Payroll
HSBC       8372933       100        Monthly, Payroll
Wells      09837         800        -
Chase      8730923       250        Monthly

The second is a master-detail list:

Name          AcctNum    Balance
------------|----------|----------
Payroll (2)              500
  Citi        930938     400
  HSBC        8372933    100         
..................................
Monthly (2)              350
  HSBC        8372933    100         
  Chase       8730923    250
..................................
Wells         09837      800

For the master-detail, my plan was just to get a big result set from the db, and munge it in php as needed. Since there's going to be some significant post-processing in php anyway, maybe I should just do three separate queries and do the joining there. (Since I'm more comfortable with that language.)

Answer 1

SELECT act.acctid AS AcctId, bankName, acctNumber, Balance, 
       NVL(jag.gid, '-') AS GroupID, NVL(gp.groupname, '-') AS GroupName
FROM accounts act 
     LEFT OUTER JOIN JointAccountGroups jag ON (act.id = jag.aid) 
     LEFT OUTER JOIN AccounGroups gp ON (jag.gid = gp.id)

NVL is a function that means "if first argument is null, return the second argument; otherwise, return first argument". NVL happens to be how Oracle do it -- all DBs have something like that, but it doesn't have a standard name; look up how Access does it.

Answer 2

SQL Server uses ISNULL() for that purpose. I'm not sure whether that works in Access.

Answer 3

Yeah, I'll use the presentation layer for the NULL.

But I must be missing something. I'm getting the same error from yours as from my original attempts:

Syntax error (missing operator) in query expression '(act.id = jag.aid) 
                  LEFT OUTER JOIN accountgroups gp ON (jag.gid = gp.id)'

Answer 4

How about:

SELECT act.acctid AS AcctId, bankName, acctNumber, Balance, 
      jag.gid AS GroupID, gp.groupname AS GroupName
FROM accounts AS act 
     LEFT OUTER JOIN JointAccountGroups AS jag ON act.id = jag.aid
     LEFT OUTER JOIN AccounGroups AS gp ON jag.gid = gp.id

Does this give you an error? An error that is easier to figure out perhaps?

Answer 5

I think in Access you may want to try something like:

FROM (accounts AS act 
     LEFT OUTER JOIN JointAccountGroups AS jag ON act.id = jag.aid)
     LEFT OUTER JOIN AccounGroups AS gp ON jag.gid = gp.id

I don't know why the parenthesis matter, but I tried a test with that and it seemed to fix it.

Answer 6

This

SELECT a.BankName, a.AcctNumber, a.Balance, ag.GroupName
FROM (Accounts a 
      LEFT JOIN JoinAccountsGroups jag 
      ON a.ID = jag.AID) 
      LEFT JOIN AccountGroups ag
      ON jag.GID = ag.GroupName;

Will select the data for the first table, however to concatenate the groups (Monthly, Payroll), you would need a User Defined Function (UDF), wich would not be available to Jet, so processing in PHP would be necessary.

You may wish to read Understanding SQL Joins. It refers to MySQL but applies to Jet, for the most part.

Answer 7

Another Try:

SELECT act.acctid AS AcctId, bankName, acctNumber, Balance, 
    jag.gid AS GroupID, gp.groupname AS GroupName
FROM accounts AS act 
   LEFT OUTER JOIN JointAccountGroups AS jag ON act.id = jag.aid
   LEFT INNER JOIN AccounGroups AS gp ON jag.gid = gp.id

Access might be having trouble with the two OUTER JOINS so I made the second one an INNER JOIN which should work

Answer 8

Another thought... why not use the Query Designer in Access. This should take about 30 seconds to design the "View". Then go look at the SQL it wrote.

Answer 9

Not only use the query designer as suggested earlier, but also use the MS Access relationship tool to record the relationship between the two foreign keys (AID, GID) and the primary keys they reference.

This makes doing natural joins in the query designer almost child's play. You can even use a query wizard in the situation you outlined.

Once you have the query built, why not use the query as a record source instead of using the tables?

The one thing I would do in PHP would be converting the multiple results into a comma separated list.