I am trying to list all projects and where there is a record in the status table, I want to pull in this data. However, in the status table there might be several entries, so I only want the latest.

tbl_project
project
A
B
C

tbl_status
project Status timestamp
A Good jan 1
A Bad Feb 1
B Good Mar 1

Require Output
project Status timestamp
A Bad Feb 1
B Good Mar 1
C

SELECT tbl_project.project, tbl_status_archive.*
FROM tbl_project LEFT JOIN tbl_status_archive ON tbl_project.project_id = tbl_status_archive.project_id;

I can't figure out how to isolate the project A

What I currently get

project Status timestamp
A Good jan 1
A Bad Feb 1
B Good Mar 1
C

http://www.w3schools.com/sql/sql_orderby.asp


getting only one record will depend on what database server you are using.

I am using phpMySQL

ORDER BY only sorts the list in a given order. I am looking to remove the duplicate values only showing the last entry unless of cause the there is not record in which case the parent record should be displayed showing blank as a response for the items in subtable

PHP is a programming language, MySQL is the database server phpmysql doesn't exist

SELECT fieldlist FROM table ORDER BY DATE LIMIT 1;

I tried as you suggested but what I got was only one record. Played around a bit more and came up with this "

'SELECT tbl_project.*, tbl_status_archive.* '.
'FROM tbl_project LEFT JOIN tbl_status_archive ON tbl_project.project_id = tbl_status_archive.project_id '.
'GROUP BY tbl_project.project_id ';"

However, it returns the first record that is comes to in the child table rather then the most recently update record. So I am still struggling to find the the record from the child table that was most recently updated.

Originally Posted by dgkindy

ORDER BY only sorts the list in a given order. I am looking to remove the duplicate values only showing the last entry unless of cause the there is not record in which case the parent record should be displayed showing blank as a response for the items in subtable

You can't cover all those criteria easily.

A JOIN will have duplicates, and a LEFT or RIGHT JOIN will leave out records with no match.

subqueries is probably the way to

Okay, I have been trying to figure this out. So to your suggestion, I am simplifying.

project Status timestamp
A Good jan 1
A Bad Feb 1
B Good Mar 1



SELECT * FROM `tbl_status_archive` ORDER BY `date_stamp` DESC LIMIT 1

So I tried this statement and the result was as follows:

project Status timestamp
B Good Mar 1

I would like it to show

project Status timestamp
A Bad Feb 1
B Good Mar 1

Of course, I cannot just say limit two as I do not know how may records could be available.

Alright, got it working but not the way I was expecting and perhaps this is what chrishirst was suggesting.

Essentially, I just walked through the first list and each time I would query the second table using the limit to restrinct the result to only one record.

Hi ,

From your Question I understood AS This is the Result You Expect


Each Project Name,And Its Latest Status, And Its Latest Date

Ex Result:
A Bad Feb 1
B Good Mar 1
C

So try the below ,

SELECT
tbl_project .project , tableOfProjectAndLatestTmpStmp.*
FROM tbl_project LEFT JOIN
(
SELECT project, MAX(timestamp) FROM tbl_status
GROUP BY project
) tableOfProjectAndLatestTmpStmp

ON tbl_project .project= tableOfProjectAndLatestTmpStmp.project

thanks,
Rajiv kumar .B

hi

I m sorry ,My previous query does Not include Status column,

Please find the below corrected Query

SELECT
tbl_project .project , tableOfProjectAndLatestTmpStmp.TmpStamp,
FROM tbl_project LEFT JOIN
(
SELECT project, MAX(timestamp) AS [TmpStamp] FROM tbl_status
GROUP BY project
) tableOfProjectAndLatestTmpStmp
LEFT JOIN
(
SELECT project, timestamp, status FROM tbl_status
)tableOfProjectAndStatus

ON tbl_project .project= tableOfProjectAndLatestTmpStmp.project AND
tableOfProjectAndLatestTmpStmp .project = tableOfProjectAndStatus.project AND
tableOfProjectAndStatus.timestamp = tableOfProjectAndLatestTmpStmp.[TmpStamp]