3NF Database Query With Joins

Question

1.00/5 (1 vote)

See more:

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!

Posted 26-Jun-13 4:31am

Jack R. Schaible

Updated 26-Jun-13 4:48am

Nelek

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2013-06-26T04:50:00

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

SQL

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

Jack R. Schaible · Answer 2 · 2013-06-26T16:01:00

Solution 2

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/[^]

Posted 26-Jun-13 16:01pm