[RESOLVED] SELECT and LEFT JOIN problems

blckspder

Hello,
Im having problems trying to pull out the DescOfRes field, there are duplicate entries of this field in the database... so i need to pull a distinct one which i have tried... I also tried pulling all individual fields in the select statement using this format -> tablename.fieldname and that didnt work.. so here is my code as it was when i added the left join to pull the DescofRes field... thanks for the help..

<?php
//pull submitted tickets from main table
$ticket_pull = "SELECT * FROM ticketMain LEFT JOIN tickStaffAssign ON tickStaffAssign.ticketNum=ticketMain.ticketNum LEFT JOIN userID ON ticketMain.userID=userID.IDnumber LEFT JOIN ticketResolve ON ticketMain.ticketNum=ticketResolve.ticketNum WHERE ticketMain.closeDate!='0000-00-00' AND tickStaffAssign.status='0' ORDER BY closeDate DESC";

$ticket_query = mysql_query($ticket_pull);

while($data = mysql_fetch_array($ticket_query)) {
	print '<b>Ticket Number</b>&nbsp;'. $data['ticketNum'] .'<br /><b>Contact</b>&nbsp;'. $data['contact'] .'<br /><b>User Id:</b>&nbsp;'. $data['userID'] .'<br /><b>Creation Date:</b>&nbsp;'. $data['createDate'] .'<br /><b>Assigned To:&nbsp;</b>'. $data['staffAssign'] .'<br /><b>Assigned Date:&nbsp;</b>'. $data['dateStart'] .'<br /><b>Close Date:</b>&nbsp;'. $data['closeDate'] .'<br /><b>Closed Status:</b>&nbsp;'. $data['closeStat'] .'<br /><b>Description Of Resolution:</b><br />'. $data['DescOfRes'];

print '<p>&nbsp;</p>';
}
?>

ahundiak

Are you saying that more than one table has a column called DescOfRes?

If so then you are on the right track in thinking that you need to use table_name.column_name in your query.

Can you post the query in which you tried specifying each field individually?

And maybe the mysql_error message?

blckspder

Sorry i dont have the other query anymore... DescOfRes field is only in one table... but we are trying to set up relationships... so when you look in the database at the ticketResolve table and look for say ticket number 12 you see...

ticketNum | ResolutionCode | DescOfRes

12 | 4 | this is a test

12 | 5 | this is a test

I am just trying to pull the DescOfRes field for the ticket number selected.. and I only want one to show up instead of say 2. and I need a way to do that in the query I showed above... is there any way to do that? hope this explains it better thanks for replying.

ahundiak

Not sure if it can be done in one query or not. Suspect not since the system would not know which record you wanted.

If there is a small number of possible ResolutionCodes then you can do it by adding a join for each ResolutionCode.

blckspder

ok thanks.. ill try that