HELP... SQL Query

[ooiwc]

Hi,

Need help on the SQL statement.
Let's say I have 2 table, (Profile & Transaction)

Profile
ID Name
001 John
002 Mary

Transaction
ID Book Date
001 Xyz 10/1/2007
001 Abc 10/3/2007
001 Cde 10/5/2007
002 Lmn 10/3/2007
002 KKK 10/6/2007

How can i create a sql query to combine both table and return only one record which show the last book that the customer rent
Example of expected result
ID Name Book Date
001 John Cde 10/5/2007
002 Mary KKK 10/6/2007

I'm try for whole day but still can't get any solution... please help...

[code green]

This is of the top of my head but may be worth a try

SELECT `name`,`book`,`date` FROM `profile`
JOIN `transaction` USING(`id`)
GROUP BY `transaction`.`id`
ORDER BY `date` DESC

[amitpatel66]

Hi,

Need help on the SQL statement.
Let's say I have 2 table, (Profile & Transaction)

Profile
ID Name
001 John
002 Mary

Transaction
ID Book Date
001 Xyz 10/1/2007
001 Abc 10/3/2007
001 Cde 10/5/2007
002 Lmn 10/3/2007
002 KKK 10/6/2007

How can i create a sql query to combine both table and return only one record which show the last book that the customer rent
Example of expected result
ID Name Book Date
001 John Cde 10/5/2007
002 Mary KKK 10/6/2007

I'm try for whole day but still can't get any solution... please help...

Try this query:

 
SELECT p.id,p.name,t.book,t.date FROM profile p,transaction t WHERE t.date = (SELECT MAX(date) FROM transaction WHERE id = p.id GROUP BY id)
 
 

[ooiwc]

Thanks a lot... It's work and that's what I want. :D