I have built a relational DB structure for a lead management system I'm working on. I have a table for leads, contacts, accounts and users. In this case, I am capturing the contact_id w/ the lead records, and I'm trying to write a SQL query that will look to pull out the user_id (and other information) for the customer service rep tied to the account, where every contact is tied to an account.
I know I'll have to do it w/ some kind of join query, but I've definitely botched it up. I tried this:
SELECT
lms_users.*
FROM
lms_leads
LEFT JOIN
lms_contacts ON lms_leads.lead_contact_id=lms_leads.lead_contact_id
LEFT JOIN
lms_accounts ON lms_accounts.account_id=lms_contacts.contact_account_id
LEFT JOIN
lms_users ON lms_users.user_id=lms_accounts.account_customer_service_rep_id
WHERE
lms_leads.lead_id='".$_POST['lead_id']."'
And when I execute that query, I get four rows. I get the correct user record I'm looking for, but I get it twice. And then I also get two other rows w/ NULL values all the way across each row.
Can anyone out there take a look at my query and see what I might be doing wrong? For each lead we have one contact_id assigned. So I need to find the account_id that contact is assigned to, and then pull all the user information from the database for the account_customer_service_rep_id assigned to that account.
Make sense? Please help.
