基本的多对多sql选择查询

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.

我正在使用MS Access,FWIW。此外,这适用于低带宽情况,因此代码优化不如简单/可读性那么重要。

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

我使用php作为表示层,因此Access的简单结果很好。

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.)

对于master-detail,我的计划只是从db中获取一个大的结果集,并根据需要在php中进行挖掘。既然在php中会有一些重要的后期处理,也许我应该做三个单独的查询并在那里加入。 (因为我对这种语言比较熟悉。)

9 个解决方案

#1

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.

将为第一个表选择数据,但是为了连接组(每月,工资单),您将需要一个用户定义函数(UDF),这对Jet不可用,因此需要在PHP中进行处理。

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

您可能希望阅读了解SQL连接。它指的是MySQL,但大多数情况下适用于Jet。

#2

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.

另一个想法...为什么不在Access中使用查询设计器。这应该花费大约30秒来设计“视图”。然后去看看它写的SQL。

#3

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

SQL Server为此目的使用ISNULL()。我不确定这是否适用于Access。

#4

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.

NVL是一个函数,意思是“如果第一个参数为null,则返回第二个参数;否则,返回第一个参数”。 NVL恰好是Oracle的做法 - 所有数据库都有类似的东西,但它没有标准名称;查看Access如何做到这一点。

#5

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

是的,我将使用表示层为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)'

#6

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?

这会给你一个错误吗?可能更容易弄清楚的错误?

#7

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

我认为在Access中你可能想尝试类似的东西:

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.

我不知道为什么括号很重要,但我尝试了一个测试,它似乎解决了它。

#8

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

访问可能有两个OUTER JOINS的问题所以我使第二个成为INNER JOIN应该可以工作

#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.

不仅使用前面建议的查询设计器,还使用MS Access关系工具记录两个外键(AID,GID)和它们引用的主键之间的关系。

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.

我将在PHP中做的一件事是将多个结果转换为逗号分隔列表。

#1