Hi, This is for an assignment and I will reference your help I am after getting the top 3 products bought for each customer in the database, I know i need a right/left join between customer and product but I am unsure how to achive this, could somebody give me some advice please? Code: SELECT CONCAT(tuser.title, ' ', tuser.FirstName, ' ', tuser.lastname) AS Customer, tproduct.Product FROM tuser, tproductorder, tproduct, torder, tcustomer WHERE tuser.userid = tcustomer.Customerid AND tcustomer.customerid = torder.customerid AND torder.orderid = tproductorder.orderid AND tproductorder.productid = tproduct.productid Thank you DT.
In MySQL a join is default a 'left' join. A join looks like this: SELECT left_table.field1, left_table.field2, right_table.fieldn FROM left_table JOIN right_table ON left_table.left_join_field = right_table.right_join_field WHERE some_field = 'freddo' LIMIT 0,3 Trying to word how to explain the difference between a left and a right join. Try this. Sometimes your 'right' table might not match a join for a record from the 'left' table. A left join will show all the entries in the left table, and put 'NULL' in the value of the fields that should be from the right table. A right join will show all the entries in the left table BUT only when the right table's fields are not NULL. PS. you asked for the top three - limit 0,3 is the way to get that in MySQL, the 'TOP' command doesn't work. Also, top by what definition? Add an ORDER BY clause to define the order and it'll affect the three answers you are given.