Help needed with table join

[mstembri]

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:

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:

$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

[FireFly]

One approach to this problem would be JOINing the two tables in the following fashion:

	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:

	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).

[DaveMaxwell]

Changing the SELECT to a SELECT DISTINCT should get you the results you want....