montymoose
Posts: 85
|
| Posted: 01/15/2008, 11:04 AM |
|
Hi folks... I'm a little stuck with a bit of SQL (PHP/MYSQL latest versions)
Considor the following tables:
TABLE USERS
id name
1 Bob
2 John
3 Sam
4 Phil
TABLE NEWS ITEMS
id user title date
1 1 Item 1 01/01/08
2 1 Item 2 19/01/08
3 2 Item 3 13/01/08
4 4 Item 4 05/01/08
I need to create a grid, which lists the users alongside just the latest news item the user has posted, the results should be as follows:
Bob - item 2 19/01/08
John - item 3 13/01/08
Sam -
Phil - Item 4 05/01/08
Whatever I try doesn't seem to work. Any suggestions gratefully recieved.
Cheers,
M00S3
|
 |
 |
DonB
|
| Posted: 01/15/2008, 8:15 PM |
|
SELECT u.name, n.title, n.date
FROM users as u
INNER JOIN news as n
WHERE n.id = (SELECT MAX(n2.id) FROM news as n2 WHERE n2.user = u.id)
GROUP BY n.id
should work
--
DonB
http://ccswiki.gotodon.net
"montymoose" <montymoose@forum.codecharge> wrote in message
news:5478d03a3eec40@news.codecharge.com...
> Hi folks... I'm a little stuck with a bit of SQL (PHP/MYSQL latest
> versions)
>
> Considor the following tables:
>
> TABLE USERS
> id name
> 1 Bob
> 2 John
> 3 Sam
> 4 Phil
>
> TABLE NEWS ITEMS
> id user title date
> 1 1 Item 1 01/01/08
> 2 1 Item 2 19/01/08
> 3 2 Item 3 13/01/08
> 4 4 Item 4 05/01/08
>
>
> I need to create a grid, which lists the users alongside just the latest
> news
> item the user has posted, the results should be as follows:
>
> Bob - item 2 19/01/08
> John - item 3 13/01/08
> Sam -
> Phil - Item 4 05/01/08
>
> Whatever I try doesn't seem to work. Any suggestions gratefully recieved.
>
> Cheers,
>
> M00S3
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
datadoit
|
| Posted: 01/16/2008, 6:08 AM |
|
DonB wrote:
> SELECT u.name, n.title, n.date
> FROM users as u
> INNER JOIN news as n
> WHERE n.id = (SELECT MAX(n2.id) FROM news as n2 WHERE n2.user = u.id)
> GROUP BY n.id
>
> should work
>
----------------------------
This does work, but how can you get the 'Sam' row as shown in the example?
|
|
|
|
DonB
|
| Posted: 01/16/2008, 6:54 AM |
|
A LEFT JOIN will include the user ('left table' row) when the corresponding
news row is missing (null)
SELECT u.name, n.title, n.date
FROM user as u
LEFT JOIN news as n
ON u.id = n.user
WHERE n.id is null OR n.id = (SELECT MAX(n2.id) FROM news as n2 WHERE
n2.user = u.id)
GROUP BY n.id
(I neglected to type the 'ON' clause before, BTW)
--
DonB
"datadoit" <datadoit@forum.codecharge> wrote in message
news:fml35r$s2p$1@news.codecharge.com...
> DonB wrote:
>> SELECT u.name, n.title, n.date
>> FROM users as u
>> INNER JOIN news as n
>> WHERE n.id = (SELECT MAX(n2.id) FROM news as n2 WHERE n2.user = u.id)
>> GROUP BY n.id
>>
>> should work
>>
> ----------------------------
>
> This does work, but how can you get the 'Sam' row as shown in the example?
|
|
|
|
datadoit
|
| Posted: 01/16/2008, 7:16 AM |
|
DonB wrote:
> A LEFT JOIN will include the user ('left table' row) when the corresponding
> news row is missing (null)
>
> SELECT u.name, n.title, n.date
> FROM user as u
> LEFT JOIN news as n
> ON u.id = n.user
> WHERE n.id is null OR n.id = (SELECT MAX(n2.id) FROM news as n2 WHERE
> n2.user = u.id)
> GROUP BY n.id
>
> (I neglected to type the 'ON' clause before, BTW)
>
-----------------------------
DonB has one big fat brain on him!
Tack on 'ORDER BY n.date DESC' to get the sort order.
|
|
|
|
montymoose
Posts: 85
|
| Posted: 01/17/2008, 7:48 AM |
|
Thanks guys...
|
|
|
|
|
