|21st Nov 2012, 21:13||#1|
Join Date: Apr 2010
Location: United Kingdom
MYSQL Left / right join help
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?
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
|22nd Nov 2012, 14:39||#3|
Hunting Wabbits since the 80s
Join Date: Mar 2001
Location: Somewhere near a dremel in Bristol
In MySQL a join is default a 'left' join.
A join looks like this:
SELECT left_table.field1, left_table.field2, right_table.fieldn
JOIN right_table ON left_table.left_join_field = right_table.right_join_field
WHERE some_field = 'freddo'
Trying to word how to explain the difference between a left and a right join.
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.
Datacentre architect and engineer extraordinaire. On my good days.