MySQL查询多对多关系。 [英] MySQL query for a many to many relation.

问题描述

我有三张桌子



user_branch中的分支基本上是位置，据说是A，B，C

user_acct_type中的帐户类型是帐户类型可能是D，E，F，G，H

 user_branch 
 
 -------- -------------------------- 
 Sr-no |用户名|分公司| 
 ---------------------------------- 
 1 |用户1 | A | 
 2 |用户1 | B | 
 3 |用户2 | A | 
 4 |用户2 | C | 
 --------------------------------- | 
 
 user_acct_type 
 
 --------------------------------- -  
 Sr-no |用户名| Acct_type | 
 ----------------------------------- 
 1 |用户1 | D | 
 2 |用户1 | E | 
 3 |用户2 | F | 
 4 |用户2 | G | 
 ----------------------------------- | 
 
 cust_code 
 
 --------------------------------- ------------------------------------------ 
 Sr-no |客户名称|客户代码|分公司|帐户类型| 
 ---------------------------------------------- ----------------------------- 
 1 |客户1 | 1234 | A | D | 
 2 |客户2 | 1235 | C | F | 
 3 |客户3 | 1236 | B | G | 
 4 |客户4 | 1237 | A | H | 
 ---------------------------------------------- ---------------------------- | 

基本上我的要求是用户1应该只能搜索那些分支是A或B的客户名称（如表user_branch中所指定的），同时只搜索帐户类型为D或E的客户名称（在user_acct_type中指定。我无法对任何查询进行qrite，以便在单个查询中满足所有条件。任何帮助表示赞赏。谢谢





编辑我尝试了查询

 选择 c.Cust_Name  as 客户
 来自 custcode c 
 内部  join  user_branch b  on  c.branch = b。分支
 内部 加入 user_acctype a   c.Cust_typeofaccount = a.acctype 
  order   by  c.Cust_Name 

现在我的问题是如何根据用户会话过滤掉结果？

解决方案

< blockquote>

  SELECT  a .Sr-no`，.Customer Name`，a .Customer Code`  FROM `cust_code` a，`user_acct_type` b，`user_branch `c  WHERE  b。 
`Acct_type` = a .Acct_type`  AND  c .Branch` = a .Branch`  AND  b .Username` = c .Username`  AND  c .Username` = ' 用户1' 

I have three tables

Branch in user_branch is basically locations supposedly A,B,C
Account type in user_acct_type is account type may be D,E,F,G,H

user_branch

----------------------------------
Sr-no   |   Username  |  Branch  |
----------------------------------
1       |   User 1    |  A       |
2       |   User 1    |  B       |
3       |   User 2    |  A       |
4       |   User 2    |  C       |
---------------------------------|

user_acct_type

-----------------------------------
Sr-no   |   Username  |  Acct_type |
-----------------------------------
1       |   User 1    |  D         |
2       |   User 1    |  E         |
3       |   User 2    |  F         |
4       |   User 2    |  G         |
-----------------------------------|

cust_code

---------------------------------------------------------------------------
Sr-no   |   Customer Name  |  Customer Code |  Branch   |  Account Type   |
---------------------------------------------------------------------------
1       |   Customer 1     |  1234          |  A        |    D            |
2       |   Customer 2     |  1235          |  C        |    F            |
3       |   Customer 3     |  1236          |  B        |    G            |
4       |   Customer 4     |  1237          |  A        |    H            |
--------------------------------------------------------------------------|

Basically my requirement is that User 1 should be able to search for only those customers names whose branch is A or B(as specified in the table user_branch) and at the same time only those customer names whose account type is D or E(as specified in user_acct_type). I am not able to qrite any query such that all the conditions are satisfied in a single query. Any help is appreciated. Thanks


Edit I tried the query

select c.Cust_Name as Customer
from custcode c
inner join user_branch b on c.branch = b.branch
inner join user_acctype a on c.Cust_typeofaccount  = a.acctype
order by c.Cust_Name

Now my question is how do I filter out the results based on user session?

解决方案

SELECT a.`Sr-no`, a.`Customer Name`, a.`Customer Code` FROM `cust_code` a, `user_acct_type` b, `user_branch` c WHERE b.
`Acct_type`=a.`Acct_type ` AND c.`Branch`=a.`Branch` AND b.`Username`=c.`Username` AND c.`Username`='User 1'

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