带连接的3NF数据库查询 [英] 3NF Database Query With Joins

问题描述

所以，我有一个像这样的数据库结构：

消息
 -------- ----- 
消息ID（PK）
文本
等等... 

< pre lang =text> MessageContact
--------------
MessageID（FK）
ContactID（FK）

< br $>

联系人
 --------- 
联系ID 
 FirstName 
 LastName 

我的MessageContact表连接Messages和Contacts表。消息可能包含许多已发送的联系人，并且联系人可能已向其发送了许多消息。我的网络表单允许用户选择多个联系人并搜索具有该组联系人的所有消息。我的问题是我不知道如何形成查询来执行这样的搜索。我正在考虑做一些事情，把联系人作为一个数组循环，消除消息记录，因为没有找到某些联系人，但这意味着拉动数据库中的所有消息，这似乎是错误的。



任何帮助将不胜感激。谢谢！

解决方案

如果可以，请避免循环遍历数组或表。尝试类似

  SELECT  M. *   -   替换为您需要的消息列表 
，C。*   -   替换为您需要的联系人列表 
  FROM 消息M 
  INNER   JOIN  MessageContact MC  on  M.MessageID = MC.MessageID 
  INNER   JOIN 联系人C  on  C.ContactID = MC.ContactID 
  WHERE  ContactID  in （...）
  ORDER   BY  ContactID，MessageID 

您将需要替换上面的（...）以逗号分隔的从您的网络表单中选择的联系人列表，例如（'1'，'2'，'3'）

或

您可以将联系人插入临时表（例如SelectedContacts）并使用

 WHERE中的ContactID（从SelectedContacts中选择ContactID）

这是有点受损，因为我没有任何东西可以在这台PC上运行它。我试图说只是返回已发送到列出的联系人的邮件，并且这些邮件的收件人数与联系人数相同。绝对有一种更为简洁的方法！

 SELECT M. *  - 替换你需要的消息列表
，C。*  - 替换来自联系人的东西列表你需要
来自消息M 
 INNER JOIN MessageMtact MC on M.MessageID = MC.MessageID 
 INNER JOIN联系人C on C.ContactID = MC.ContactID 
 WHERE M.MessageID in 
（从MessageContact中选择MessageID M 
其中ContactID在
中（从SelectedContacts中选择ContactID）
 Group by MessageID 
有count（*）= （从SelectedContacts选择countC（*）作为ContactCount））

我最终在这里找到了一篇文章，解释了如何在多个上执行多种类型的查询-multi table join： http：// www.sergiy.ca/how-to-write-many-to-many-search-queries-in-mysql-and-hibernate/ [ ^ ]

So, I have a database structure like so:

Messages
-------------
MessageID (PK)
Text
Etc...

MessageContact
--------------
MessageID (FK)
ContactID (FK)

Contacts
---------
ContactID
FirstName
LastName

My MessageContact table joins the Messages and Contacts tables. A message may have many contacts it was sent to, and a contact may have had many messages sent to them. My web form allows a user to select a number of Contacts and search for all messages with that set of contacts. My problem is I have no idea how to form the query to perform such a search. I was thinking of doing something with taking in the Contacts as an array an looping through, eliminating message records as certain contacts weren't found, but that would mean pulling all the messages in the database, and that seems wrong.

Any help would be appreciated. Thanks!

解决方案

Avoid looping through an array or table if you can. Try something like

SELECT M.*  -- replace with list of stuff from Messages you need
, C.*   -- replace with list of stuff from Contacts you need
FROM Messages M
INNER JOIN MessageContact MC on M.MessageID=MC.MessageID
INNER JOIN Contacts C on C.ContactID=MC.ContactID
WHERE ContactID in (...)
ORDER BY ContactID, MessageID

You will need to replace the (...) above with a comma-separated list of the contacts selected from your web form e.g. ('1','2','3')
OR
you could insert the contacts into an interim table (e.g. SelectedContacts) and use

WHERE ContactID in (Select ContactID from SelectedContacts)

[Edit - see comments below]
This is a bit mangled as I haven't got anything to run it against on this PC. I'm trying to say just return messages that have been sent to the listed contacts and where the number of recipients for those messages is the same as the count of contacts. There is definitely a tidier way of doing it!

SELECT M.*  -- replace with list of stuff from Messages you need
, C.*   -- replace with list of stuff from Contacts you need
FROM Messages M
INNER JOIN MessageContact MC on M.MessageID=MC.MessageID
INNER JOIN Contacts C on C.ContactID=MC.ContactID
WHERE M.MessageID in
    (Select MessageID from MessageContact M
        where ContactID in
         (Select ContactID from SelectedContacts)
    Group by MessageID
    Having count(*) = (select count(*) as ContactCount from SelectedContacts))

I eventually found an article here that explains how to do several types of queries on a multi-to-multi table join: http://www.sergiy.ca/how-to-write-many-to-many-search-queries-in-mysql-and-hibernate/[^]

这篇关于带连接的3NF数据库查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！