Mysql help

Databases
1

Hey everyone, have a quick question, the below query works, but for one result its not really working lol… it isnt pulling the “sr_date_modified” user, instead it pulls the user that is assigned to this “ticket”, so if i change the user that is assigned to the ticket, it also uses that person as the person the created it and modified it… (please ignore the datatypes, those are not final) thanks for any help you can provide.


SELECT sr.*
,cc.contact_id
,cc.contact_name
,u.id AS uid
,u.firstName
,u.lastName
,c.company_name
FROM
company_sr sr
JOIN
company_contacts cc ON sr.sr_company_contact = cc.contact_id
JOIN
users u ON sr.sr_assigned_to = u.id
JOIN
companies c ON sr.company_id = c.company_id
WHERE
sr.sr_id = {$id}
 



CREATE TABLE IF NOT EXISTS `company_sr` (
  `sr_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` char(50) DEFAULT NULL,
  `sr_status` varchar(50) DEFAULT 'New',
  `sr_invoice_num` varchar(50) DEFAULT NULL,
  `sr_assigned_to` varchar(20) DEFAULT 'Signup',
  `sr_company_contact` varchar(50) DEFAULT NULL,
  `sr_datework` varchar(255) DEFAULT NULL,
  `sr_rate` varchar(50) DEFAULT NULL,
  `sr_time_start` varchar(50) DEFAULT NULL,
  `sr_ampm1` varchar(50) DEFAULT NULL,
  `sr_time_end` varchar(50) DEFAULT NULL,
  `sr_ampm2` varchar(4) DEFAULT NULL,
  `sr_totalhours` varchar(255) DEFAULT NULL,
  `sr_subject` varchar(50) DEFAULT NULL,
  `sr_resolv` varchar(255) DEFAULT NULL,
  `sr_date_created` varchar(255) DEFAULT NULL,
  `sr_created_by` varchar(50) DEFAULT NULL,
  `sr_date_modified` varchar(255) DEFAULT NULL,
  `sr_modified_by` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`sr_id`)



CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `firstName` varchar(255) DEFAULT NULL,
  `lastName` varchar(255) DEFAULT NULL,
  `engineer` varchar(50) DEFAULT NULL,
  `billing_group` tinyint(50) DEFAULT NULL,
  `group` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
2 
SELECT sr.*
     , cc.contact_name
     [COLOR="Red"], uc.firstName AS firstname_created_by
     , uc.lastName  AS lastname_created_by[/COLOR]
     , [COLOR="Blue"]ua.firstName AS firstname_assigned_to
     , ua.lastName  AS lastname_assigned_to[/COLOR]
     , c.company_name
  FROM company_sr AS sr
INNER
  JOIN company_contacts AS cc 
    ON cc.contact_id = sr.sr_company_contact
[COLOR="Red"]INNER
  JOIN users AS uc 
    ON uc.id = sr.sr_created_by  [/COLOR]
[COLOR="Blue"]INNER
  JOIN users AS ua 
    ON ua.id = sr.sr_assigned_to [/COLOR]
INNER
  JOIN companies c 
    ON c.company_id = sr.company_id 
 WHERE sr.sr_id = {$id}
3

Thank you so much Rudy… I did’nt think it would be that easy, thought I might have to use a sub query… guess I need to purchase your book hehe