Entity association problem

Question

I have three tables:

User:  UserId (pk)
       FirstName
       Lastname

Messages: MessageId (pk)
          Heading
          Body

User_Messages: UserId 
               MessageId

Now, the entity designer only creates two tables with an association bewteen the tables. I am trying to select an item where UserId = value1 and MessageId = value2, but cant seem to get it right. What would theentity query look like?

Answer 1

Select a.firstname, a.lastname, b.heading, b.body
from user a, Messages b, User_Messages c
where a.UserId = c.UserId and b.MessageId = c.MessageId
and c.UserId = value1 and c.MessageId = value2

Answer 2

To select only users/messages, which are associated to each other, you can use

SELECT u.UserId, u.FirstName, u.LastName, m.MessageId, m.Heading, m.Body
FROM User_Messages h
   INNER JOIN User u ON h.UserId = u.UserId
   INNER JOIN Messages m ON h.MessageId = m.MessageId

To select all users (and adding their messages, if any) use:

SELECT u.UserId, u.FirstName, u.LastName, m.MessageId, m.Heading, m.Body
FROM User u 
   LEFT JOIN User_Messages u ON h.UserId = u.UserId
   LEFT JOIN Messages m ON h.MessageId = m.MessageId

Finally, to select messages, and add user information for each of them, use:

SELECT u.UserId, u.FirstName, u.LastName, m.MessageId, m.Heading, m.Body
FROM Messages m 
   LEFT JOIN User_Messages h ON h.MessageId = m.MessageId
   LEFT JOIN User u ON h.UserId = u.UserId