|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
February 15th, 2006, 11:18 PM
|
|||
|
|||
Joining 2 tables with a count and subquery?
I have this problem that i just cant seem to solve :
We are making a very simple PHP / MySql forum containing only 2 tables. 1. forum_headers. 2. forum_notes. When the user first sees the forum i would like to show: forum_headers.subject AND A count of the forum notes that have have the forum_headers.id in the forum_notes.headerId field AND The newest forum_notes.created (timestamp) again with the forum_headers.id in the forum_notes.headerId field. So that the output is something like this : Subject, Record count, Latest record for the subject. I dont know if it is possible, i have searched and searched.. Thank you in advance! Jeppe Haugstrup
|
|
#2
February 17th, 2006, 12:12 PM
|
|||
|
|||
|
Code:
SELECT
fh.subject
,COUNT(fn.record) AS cnt
,(SELECT
record
FROM
forum_notes
WHERE
headerID = fh.id
ORDER BY
forum_notes.created DESC
LIMIT 1) AS latest
FROM
forum_headers AS fh
INNER JOIN
forum_notes AS fn
GROUP BY
fh.subject
,latest
__________________
"A pawn is the most important piece on the chessboard -- to a pawn"
|
|
| Viewing: Dev Articles Community Forums > Databases > MySQL Development > Joining 2 tables with a count and subquery? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
