MySQL Query Containing Multiple Queries.

[FishSword]

Hiya,

 

I have a database that holds basic "products", "users", and "sales" information for a shop.

I am having a few problems with a MySQL query that will retrieve the following information from the appropriate tables.

• The Purchasers "Username".
  
• The "Name" of the purchased item.
  
• The "Price" of the purchased item.
  
• The "time" the item was purchased.
  

  If any items have been purchased multiple times by a purchaser, I need to only show the latest one. Can this be done?

 

The structure of the tables can be seen below.

 

Table "Products":

• product_id - Used as a unique identifier.
  
• name - The name of the product.
  
• price - The price of the product.
  

   

Table "Users":

• user_id - Used as a unique identifier.
  
• username - The username of the purchaser.
  

 

Table "Sales":

• sale_id - Used as a unique identifier.
  
• product_id - The unique identifier of the purchased product.
  
• user_id - The unique identifier of the purchaser.
  
• time - The date and time the sale took place.
   
  

Can anyone help?

Any Help is Much Appreciated!

 

Cheers

[Muddy_Funster]

this is un-tested, but should give the ground work for you to get a working query:

SELECT Users.username, Products.name, Products.price, Sales.time 
FROM Users INNER JOIN Sales ON ( Users.user_id = Sales.sales_id) Products INNER JOIN Sales ON (Products.product_id = Sales.product_id) 
WHERE Users.user_id = [your variable here] ORDER BY Sales.time DESC LIMIT 1