Simple SQL Query (INNER JOIN + MAX)

Dev Articles Community Forums Sponsor:

January 5th, 2005, 02:18 PM

lucasmeloch

Registered User

Join Date: Jan 2005

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

Simple SQL Query (INNER JOIN + MAX)

Compared to some of the inner join and max statments ive looked at trying to find a solution i guess this is pretty simple.

I have 2 tables:

TicketControl (TicketID, Subject, UserID, Status)

TicketComments (CommentID, TicketID, Comment, CommentDate)

I want to display everything from the ticket control file, and then display the date of the most recent comment.

I have tried adapting this statement:

SELECT ticketcontrol.*,ticketcomments.* FROM ticketcontrol INNER JOIN ticketcomments ON ticketcontrol.ticketid = ticketcomments.ticketid

But it returns details on all of the comments for each control record

January 5th, 2005, 05:42 PM

mattp23

Moderated

Join Date: Oct 2003

Location: UK

Posts: 82

Time spent in forums: 5 h 43 m 44 sec
Reputation Power: 6

I've not used INNER JOIN's before, so maybe someone else could shed some light on this, but have you tried something like this:

Code:

SELECT ticketcontrol.*, ticketcomments.* 
FROM ticketcontrol, ticketcomments 
WHERE ticketcontrol.TicketID = ticketcomments.TicketID

__________________
http://www.phptutorials.cjb.net. go on, give it a click!

January 6th, 2005, 08:53 AM

MadCowDzz

I'm Internet Famous

Dev Articles Frequenter (2500 - 2999 posts)

Join Date: Jan 2003

Location: Toronto, Canada

Posts: 2,890

Time spent in forums: 1 Week 16 h 14 m 9 sec
Reputation Power: 8

how about something like this?

Code:

SELECT ticketcontrol.ticketid, ticketcontrol.subject, 
       ticketcontrol.userid, ticketcontrol.status,     
       ticketcomments.commentdate
FROM TicketControl, TicketComments
WHERE ticketcontrol.ticketid = ticketcomments.ticketid
GROUP BY ticketcomments.commentdate

January 6th, 2005, 12:47 PM

lucasmeloch

Registered User

Join Date: Jan 2005

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

Thanks, I think that helped.

OK, so far I have:

Code:

SELECT ticketcontrol.*, ticketcomments.commentdate FROM
 ticketcontrol, ticketcomments WHERE ticketcontrol.ticketid
 = ticketcomments.ticketid GROUP BY ticketcomments.commentdate

Which im sure is an improovement on my first query however I am still getting a record returned for EACH of the comments. I only want the most recent comment for the ticket to be selected.

January 7th, 2005, 06:58 AM

mattp23

Moderated

Join Date: Oct 2003

Location: UK

Posts: 82

Time spent in forums: 5 h 43 m 44 sec
Reputation Power: 6

Order by comment date, then only use the first returned record

January 10th, 2005, 12:19 PM

lucasmeloch

Registered User

Join Date: Jan 2005

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

That wouldnt work as I need each record from the control file and only one record from the comment file

January 11th, 2005, 03:59 AM

mattp23

Moderated

Join Date: Oct 2003

Location: UK

Posts: 82

Time spent in forums: 5 h 43 m 44 sec
Reputation Power: 6

okay in that case you probably want two sql queries, one to get the details from TicketControl, the as you loop through get the latest comment for each record from TicketComments, like:

PHP Code:


		
			
 $control_sql = 'SELECT * FROM TicketControl';



$control_result = mysql_query($control_sql)

    or die('Query failed: ' . mysql_error());



while( $row= mysql_fetch_object( $control_result ) )

{

    $comment_sql = 'SELECT * FROM TicketComments WHERE TicketID = ' . $row->id;

    $comment_result = mysql_query($comment_sql)

        or die('Query failed: ' . mysql_error());

    $comment = mysql_fetch_object( $comment_result )

}

Not the nicest code i know but i'm writing it on the fly, hopfully it will give you some idea what i'm talking about.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: