SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: mysql help

  1. #1
    SitePoint Zealot Gman's Avatar
    Join Date
    Jan 2002
    Location
    Sarasota, FL
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql help

    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.


    Code MySQL:
    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}

    Code MySQL:
    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`)

    Code MySQL:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT sr.*
         , cc.contact_name
         , uc.firstName AS firstname_created_by
         , uc.lastName  AS lastname_created_by
         , ua.firstName AS firstname_assigned_to
         , ua.lastName  AS lastname_assigned_to
         , c.company_name
      FROM company_sr AS sr
    INNER
      JOIN company_contacts AS cc 
        ON cc.contact_id = sr.sr_company_contact
    INNER
      JOIN users AS uc 
        ON uc.id = sr.sr_created_by  
    INNER
      JOIN users AS ua 
        ON ua.id = sr.sr_assigned_to 
    INNER
      JOIN companies c 
        ON c.company_id = sr.company_id 
     WHERE sr.sr_id = {$id}
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot Gman's Avatar
    Join Date
    Jan 2002
    Location
    Sarasota, FL
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •