SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2000
    Location
    Norcross, GA
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help needed with table join

    I'm working on a call loggin system that utilizes call-tickets to help keep track of incidents. Each unique problem is assigned to a new ticket, and there can be unlimited notes associated with each ticket. Each note will have a userid associated with it, for tracking purposes.

    Right now I'm making a user's history screen that will display all tickets that he/she has worked on.

    I have two tables:

    Code:
    table:  call_ticket
    
    ticketid  int(5)    No    auto_increment  
    fnum  varchar(8)    No      
    categoryid  int(5)    No  0    
    statusid  int(5)    No  0    
    dateopened  datetime    No  0000-00-00 00:00:00    
    dateclosed  datetime    No  0000-00-00 00:00:00    
    extra  tinyint(1)    No  0  
    
    
    table:  call_notes
    
    noteid  int(10)    No    auto_increment  
    ticketid  int(10)    No  0    
    userid  int(5)    No  0    
    call_contact  varchar(50)    No      
    call_start  datetime    No  0000-00-00 00:00:00    
    call_end  datetime    No  0000-00-00 00:00:00    
    call_elapsed  time    No  00:00:00    
    call_seconds  int(10)    No  0    
    entered_statusid  tinyint(2)    No  0
    On my history window I want to query the database to show all tickets a user has worked on - to do this will need to take a look at call_notes to compare userid vs. the session_userid for matches, then I'll display the results from call_ticket that correspond.

    This is the SQL query I thought would work:
    Code:
    $result = mysql_query("
    	SELECT 
    		call_ticket.ticketid, 
    		call_ticket.fnum, 
    		call_ticket.categoryid, 
    		call_ticket.statusid, 
    		DATE_FORMAT(call_ticket.dateopened,'$dateformat') as fdateopened, 
    		DATE_FORMAT(call_ticket.dateclosed,'$dateformat') as fdateclosed
    	FROM 
    		call_ticket, 
    		call_notes 
    	
    	WHERE 
    		call_ticket.ticketid = call_notes.ticketid &&
    		call_notes.userid = '$userid' 
    	
    	ORDER BY 
    		call_ticket.ticketid DESC
    	
    	",$dbx);
    while ($row = mysql_fetch_array($result)) {
    The query is functional (no errors) but it does not work as intended.

    The problem is I want to limit the rows returned to one per ticket -- in other words it is unnecessary to list 2 rows for the same ticket (where the user entered multiple notes).

    I'm relatively new to SQL, and I'm not even sure I have my tables organized properly.

    Any help will be greatly appreciated.

    Thanks,
    Mike
    - Mike
    http://www.georgiaoffroad.com

  2. #2
    Custom User Title v1.0 FireFly's Avatar
    Join Date
    Aug 2001
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One approach to this problem would be JOINing the two tables in the following fashion:
    Code:
    	SELECT
    		call_ticket.ticketid,
    		call_ticket.fnum,
    		call_ticket.categoryid,
    		call_ticket.statusid,
    		DATE_FORMAT(call_ticket.dateopened,'$dateformat') as fdateopened,
    		DATE_FORMAT(call_ticket.dateclosed,'$dateformat') as fdateclosed
    
    	FROM
    		call_ticket
    
    	LEFT JOIN
    		call_notes
    		ON (call_ticket.ticketid = call_notes.ticketid AND call_notes.userid = $userid)
    
    	ORDER BY
    		call_ticket.ticketid DESC
    And then throwing in this WHERE clause:
    Code:
    	SELECT
    		call_ticket.ticketid,
    		call_ticket.fnum,
    		call_ticket.categoryid,
    		call_ticket.statusid,
    		DATE_FORMAT(call_ticket.dateopened,'$dateformat') as fdateopened,
    		DATE_FORMAT(call_ticket.dateclosed,'$dateformat') as fdateclosed
    
    	FROM
    		call_ticket
    
    	LEFT JOIN
    		call_notes
    		ON (call_ticket.ticketid = call_notes.ticketid AND call_notes.userid = $userid)
    
    	WHERE
    		call_notes.userid IS NOT NULL
    
    	ORDER BY
    		call_ticket.ticketid DESC
    That one checks to see if the JOIN was actually succeful (i.e did we found a note in call_notes table with the desired userid; if we didn't, call_notes.useid will be NULL, and the entry won't be returned).

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    Changing the SELECT to a SELECT DISTINCT should get you the results you want....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


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
  •