0

I've been trying to contrust a query to grab columns for 3 tables, but I keep getting mismatched rows.

These are my tables:

Messages
- messageID
- sender (can either be a customerID or employeeID) - receiver (can either be a customerID or employeeID)

(Note: for each message it will contain only 1 customer and 1 employee, ie. Customer's dont interact with eachother and employees don't message eachother also)

Customer
- customerID

Employee
- employeeID
- departmentID

DEPARTMENT
- departentID
- departmentName

For a particular customer with customerID = 5, I want to figure out what is the DepartmentName of the employee they were talking to.

My Intial attemp at this was: 

SELECT * FROM Messages,Employee, Departmnet, 
WHERE sender = '5' OR receiver = '5' 
AND (Employee.employeeID = Messages.sender OR Employee.employeeID = Messages.Receiver) 
AND Employee.departmentID = Department.DepartmentID;

However this returns way more rows than expected. I think it's because sender or receiver can potentially be the employeeID.

My 2nd guess is maybe i have to join tables, but i dont have much experience in this. If anyone could show me or tell me how to perform this query I would appreciate it.

Ike Walker
  • 64,401
  • 14
  • 110
  • 109
Stephan Walters
  • 343
  • 2
  • 13
  • Post some sample data and the desired result. – Mihai Nov 25 '13 at 13:30
  • 1
    customerId and employeeId are just auto increment ids or you have made sure that a customer and a employee cannot have same id? – rakeshjain Nov 25 '13 at 13:31
  • @rakeshjain they aren't auto increment values, and the way that the Messages are created ensures that a Customer will only message an employee and an employee will only message a customer. – Stephan Walters Nov 25 '13 at 14:18

3 Answers3

2

The fundamental problem is that you are missing parentheses around this clause:

sender = '5' OR receiver = '5'

However, I would recommend that you use ANSI-style joins to make the query more readable too.

This should help:

SELECT * 
FROM Messages
INNER JOIN Employee ON (Employee.employeeID = Messages.sender OR Employee.employeeID = Messages.Receiver)
INNER JOIN Department ON Employee.departmentID = Department.DepartmentID
WHERE (Messages.sender = '5' OR Messages.receiver = '5');
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
1

Did you try this:

SELECT * FROM Messages m 
INNER JOIN Employee e 
INNER JOIN Departmnet d 
ON ((m.sender=e.employeeId || m.receiver=e.employeeId) d.departmentId=e.departmentId) 
WHERE m.sender = '5' OR m.receiver = '5'
rakeshjain
  • 1,791
  • 11
  • 11
0

Your problem is actually that you can have an Employee with the same id as a Customer, with no way to tell the two apart.

I think that a better way to do this is to treat both Customers and Employees as Users, and to link the User table to a Role table that tells you whether they are a customer or an employee.

An alternative way is to have a Message table that has one field for EmployeeID and one field for CustomerID, and one field to specify whether this is customer to employee, or employee to customer. In other words, a message that was sent, or a message that was received.

Finally, if you are sure that there can be no clashes with Customer and Employee IDs, then this will probably do for your query:

SELECT * 
FROM Messages
INNER JOIN Employee
ON (
  (receiver = '5' AND Employee.employeeID = sender) OR
  (sender = '5'   AND Employee.employeeID = receiver)
)
INNER JOIN Department 
ON Employee.departmentID = Department.departmentID;

The above specifies that the receiver and sender can't both be employees or customers.

UPDATE: To clarify, a single field should not refer to two other tables, because that makes it impossible to specify it as a foreign key. There may be clashes in the identity columns of the two tables. For example, what happens if you have a Customer with ID 5, and an Employee with ID 5?

There are techniques for dealing with this situation, some of which are shown here: Foreign Key to multiple tables

Community
  • 1
  • 1
Gustav Bertram
  • 14,591
  • 3
  • 40
  • 65
  • Do not encourage te use of SQL Antipatterns like implicit joins. Let's try to take people into the 21st century. – HLGEM Nov 25 '13 at 14:13
  • 1
    Rewritten. In my defense, I would like to point out that the OP has bigger problems than implicit vs explicit joins. Like a database design that precludes the use of foreign keys. – Gustav Bertram Nov 25 '13 at 14:44
  • @GustavBertram thats a low blow.. however I am using foreign keys on my database schema.. Rather than using my exact database schema i watered-down my example to make it less confusing and more readable. – Stephan Walters Nov 25 '13 at 16:18
  • @user3032422 It was not meant as an insult. I am specifically referring to the fact that `sender` on `Message` can refer to both an `Employee` and a `Customer`. That means you can't define it as a foreign key, because it doesn't refer to a single table. – Gustav Bertram Nov 25 '13 at 17:01
  • Ahh yes, thats because it makes the Messages table simplistic. If i applied foreign keys, I would need to create a field a determine who was the sender. That will require more logic than needede. In addition, there won't be many messages. – Stephan Walters Nov 25 '13 at 19:29
  • `SELECT * FROM Employee INNER JOIN Customer ON Employee.employeeID = Customer.customerID` If that query ever returns any rows, your system **breaks**. What about the logic to stop that happening? You can fix your design by adding the extra field to see who sent the message. – Gustav Bertram Nov 25 '13 at 20:04