我有一个与数据库中大多数其他表相关的表将其添加为联接是否足以使查询按预期返回?

ars1skjm  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(173)

这是数据库的当前结构

业务登录
客户可能是每个公司都有相关信息的预定客户列表中的任何人。配置文件是他们可以用来访问其客户信息的登录帐户。
当有人登录到他们的配置文件时,只要他们有关于客户的有效信息(姓名,…),他们就可以将一个或多个客户帐户添加到他们的配置文件中
这并不常见,但有时一个客户会属于多个配置文件。
客户:

+------------------+--------------------------+-----------+------------+-----+
| id (primary key) | company_id (primary key) | firstname | lastname   | ... |
+------------------+--------------------------+-----------+------------+-----+
|               1  |                        1 | John      | Doe        | ... |
+----------------------------------------------------------------------------+
|               2  |                        1 | Jane      | Doe        | ... |
+----------------------------------------------------------------------------+
|               3  |                        2 | Elmo      | Mack       | ... |
+----------------------------------------------------------------------------+
|               1  |                        2 | Chester   | Torain     | ... |
+----------------------------------------------------------------------------+

轮廓:

+------------------+--------------------------+-----------+-------------+-----+
| id (primary key) | company_id (primary key) | username  | email       | ... |
+------------------+--------------------------+-----------+-------------+-----+
|               1  |                        1 | odoe      | j@gmail.com | ... |
+-----------------------------------------------------------------------------+
|               2  |                        1 | adoe      | d@gmail.com | ... |
+-----------------------------------------------------------------------------+
|               3  |                        2 | emlo      | e@gmail.com | ... |
+-----------------------------------------------------------------------------+
|               1  |                        2 | ches      | c@gmail.com | ... |
+-----------------------------------------------------------------------------+

(pivot)客户概况:

+------------------+-------------+-------------+
| id (primary key) | customer_id | profile_id  |
+------------------+-------------+-------------+
|               1  |          1  |           1 |
+----------------------------------------------+
|               2  |          1  |           2 |
+----------------------------------------------+
|               3  |          3  |           3 |
+----------------------------------------------+

客户和个人资料之间存在多对多关系。一个客户可以有多个概要文件,每个概要文件都可以用来访问一个或多个客户,因此可以使用透视表。
customer和profile表都有复合键(id和company\u id),由于数据的输入方式,这些键保持记录的唯一性。
还有一张公司的table
公司名称:

+------------------+---------------+------+
| id (primary key) | company_name  | ...  |
+------------------+---------------+------+
|               1  |        google | ...  |
+-----------------------------------------+
|               2  |         yahoo |  ... |
+-----------------------------------------+

company表与customer表具有一对多关系,而profile表具有一对多关系。
作为旁注,数据库中有更多的表都具有相同的复合键结构。

问题

如果我做一个普通的查询

SELECT
    c.`firstname`
,   c.`lastname`
,   p.`username`
,   p.`email`
FROM
    `customer` c
LEFT JOIN `customer_profile` cp ON (cp.`customer_id` = c.`id`)
LEFT JOIN `profile` p ON  (p.`id` = cp.`profile_id`)
WHERE  c.`id` = 1 AND c.`company_id` = 1;

我最终会得到不正确的关系,其中customer 1 company 1(john doe)会有他的概要文件和customer 1 company 2(chesters)概要文件的记录,因为透视表没有说明公司id
所需的结果将是客户的(名字、姓氏)和与该客户相关联的所有配置文件的(用户名和电子邮件)。
这就是期望的结果,给出这些数据:

+-------------+------------+-----------+--------------+
| firstname   | lastname   | username  | email        |
+-------------+------------+-----------+--------------+
| John        | Doe        | odoe      | j@gmail.com  |
+-----------------------------------------------------+
| John        | Doe        | adoe      | d@gmail.com  |
+-----------------------------------------------------+

实际结果可能包含其他记录,其中透视表添加了不应包含的关系。
但实际结果还包括:

+-------------+------------+-----------+--------------+
| firstname   | lastname   | username  | email        |
+-------------+------------+-----------+--------------+
| John        | Doe        | odoe      | j@gmail.com  |
+-----------------------------------------------------+
| John        | Doe        | adoe      | d@gmail.com  |
+-----------------------------------------------------+
| John        | Doe        | ches      | c@gmail.com  |  <--
+-----------------------------------------------------+

我需要过滤掉这样的档案和客户属于不同公司的任何记录。
为了解决这个问题,我尝试了这个查询

SELECT
    c.`firstname`
,   c.`lastname`
,   p.`username`
,   p.`email`
FROM
    `customer` c
LEFT JOIN `customer_profile` cp ON (cp.`customer_id` = c.`id`)
LEFT JOIN `profile` p ON  (c.`company_id` = p.`company_id` AND p.`id` = cp.`profile_id`)
WHERE  c.`id` = 1 AND c.`company_id` = 1;

它确实给出了期望的结果,因为它也是通过公司id键加入的这是我们目前使用的,我不能判断它是否适用于所有情况,但它还没有引起任何意外的结果。

问题

我的主要问题是我不知道该怎么处理公司的table。如果将company表连接到customer表就足以删除任何不需要的记录,而不是我当前拥有的记录?
像这样的?

SELECT
    c.`firstname`
,   c.`lastname`
,   p.`username`
,   p.`email`
FROM
    `company` co
LEFT JOIN `customer` c ON co.`id` = c.`company_id`
LEFT JOIN `customer_profile` cp ON (cp.`customer_id` = c.`id`)
LEFT JOIN `profile` p ON p.`id` = cp.`profile_id`
WHERE c.`id` = 1 AND c.company_id = 1;

看起来不是这样,因为pivot表没有company\u id记录。
此外,任何帮助寻找像我这样的情况下,我可以参考或记录将非常感谢。
编辑
将querys where子句改为按customer.id而不是firstname搜索,并将“to”改为“wuery应该是这样的”。
如果问题是在数据库的设计,这是好的,我只需要知道。使用当前的设计会更简单,但如果这是问题所在,那就更简单了。从“philipxy”注解可以看出,问题是pivot表上没有company\ id字段。
编辑
查询背后的逻辑是
当此公司的此客户(c.id和c.company\u id)具有该公司的配置文件时,获取名称和配置文件详细信息。

uubf1zoe

uubf1zoe1#

最终我认为我的问题源于糟糕的数据库设计。最后我给每个表添加了一个新的唯一主键
在客户桌上这样说:

+-----+-------------+------------------------+-------------+-----+
| id  | customer_id | company_id | username  | email       | ... |
+-----+-------------+------------+-----------+-------------+-----+
|  1  |           1 |          1 | odoe      | j@gmail.com | ... |
+----------------------------------------------------------------+
|  2  |           2 |          1 | adoe      | d@gmail.com | ... |
+----------------------------------------------------------------+
|  3  |           3 |          2 | emlo      | e@gmail.com | ... |
+----------------------------------------------------------------+
|  4  |           1 |          2 | ches      | c@gmail.com | ... |
+----------------------------------------------------------------+

这解决了透视表匹配记录的问题,因为每个客户记录都有一个唯一的键,使透视表成为一个正常的多对多关系。

相关问题