基本的多对多SQL选择查询 [英] basic many-to-many sql select query

问题描述

我认为这应该很容易，但是却在逃避我.我在帐户和帐户组之间建立了多对多关系.一个帐户可以位于零个或多个组中，因此我使用的是标准联接表.

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

我正在使用MS Access，FWIW.另外，这是针对低带宽的情况，因此代码优化不如简单性/可读性那么重要.

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

我使用php作为表示层，因此Access的基本结果很好.

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

第二个是主从列表:

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

对于主从细节，我的计划是从数据库中获取较大的结果集，并根据需要在php中进行修改.由于无论如何都会在php中进行一些重要的后处理，也许我应该只做三个单独的查询并在那里进行连接. (因为我对这种语言比较满意.)

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

推荐答案

此

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;

将为第一个表选择数据，但是要连接组(每月，工资)，您将需要一个用户定义函数(UDF)，而Jet则无法使用它，因此有必要在PHP中进行处理.

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.

您可能希望阅读了解SQL连接 .它指的是MySQL，但大部分情况下适用于Jet.

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

这篇关于基本的多对多SQL选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！