4 Replies - 439 Views - Last Post: 25 November 2013 - 01:09 PM Rate Topic: -----

#1 thacoolest  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 07-July 09

Would a JOIN clause be necessary for this type of query?

Posted 25 November 2013 - 06:31 AM

Hey yall,

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Would a JOIN clause be necessary for this type of query?

#2 DarenR  Icon User is online

  • D.I.C Lover

Reputation: 484
  • View blog
  • Posts: 3,246
  • Joined: 12-January 10

Re: Would a JOIN clause be necessary for this type of query?

Posted 25 November 2013 - 07:43 AM

you would need something that associates the customer to one of the tables
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5881
  • View blog
  • Posts: 12,759
  • Joined: 16-October 07

Re: Would a JOIN clause be necessary for this type of query?

Posted 25 November 2013 - 08:13 AM

Hmm... so, to be clear, customerID can NEVER be the same value as an employeeID? Because, if this is not true, you're screwed. How did you enforce that?

If you must do this, then add another field to Messages and go from there:
Messages
- messageID
- customerID
- employeeID
- isSenderEmployee



Now most problems are solved.

This post has been edited by baavgai: 25 November 2013 - 08:13 AM

Was This Post Helpful? 0
  • +
  • -

#4 thacoolest  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 07-July 09

Re: Would a JOIN clause be necessary for this type of query?

Posted 25 November 2013 - 12:34 PM

View Postbaavgai, on 25 November 2013 - 08:13 AM, said:

Hmm... so, to be clear, customerID can NEVER be the same value as an employeeID? Because, if this is not true, you're screwed. How did you enforce that?

If you must do this, then add another field to Messages and go from there:
Messages
- messageID
- customerID
- employeeID
- isSenderEmployee



Now most problems are solved.



It's enforced by the way the message is created. In order to create a message you have to be logged in.. so the sender will always be your customerID... in addition, when you your selecting a person to send a message to.. you will only have the option to select an employee..

That is a more highlevel explaination of how the message create works... also i received a response on stack overflow incase anyone ever anyone runs into this problem

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


Was This Post Helpful? 0
  • +
  • -

#5 DarenR  Icon User is online

  • D.I.C Lover

Reputation: 484
  • View blog
  • Posts: 3,246
  • Joined: 12-January 10

Re: Would a JOIN clause be necessary for this type of query?

Posted 25 November 2013 - 01:09 PM

I think what bag was getting it as that those tables are sloppy to start with and very poorly constructed. He gave you a very good out that would make future queries easy and streamlined to do. As those tables stand now, it will make everything hard to do in the future.

PS did a 1st grader make those tables?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1