bit-tech.net

Go Back   bit-tech.net Forums > Technology > Software

Reply
 
Thread Tools
Old 21st Nov 2012, 20:13   #1
deathtaker27
Backups ... Its a way of life
 
deathtaker27's Avatar
 
Join Date: Apr 2010
Location: United Kingdom
Posts: 1,686
deathtaker27 is a Super Spamming Saiyandeathtaker27 is a Super Spamming Saiyandeathtaker27 is a Super Spamming Saiyandeathtaker27 is a Super Spamming Saiyandeathtaker27 is a Super Spamming Saiyandeathtaker27 is a Super Spamming Saiyandeathtaker27 is a Super Spamming Saiyandeathtaker27 is a Super Spamming Saiyandeathtaker27 is a Super Spamming Saiyandeathtaker27 is a Super Spamming Saiyandeathtaker27 is a Super Spamming Saiyan
MYSQL Left / right join help

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.
deathtaker27 is offline   Reply With Quote
Old 22nd Nov 2012, 13:30   #2
Daedelus
Multimodder
 
Join Date: May 2009
Posts: 248
Daedelus has yet to learn the way of the Dremel
What do you mean by "top 3"?
Daedelus is offline   Reply With Quote
Old 22nd Nov 2012, 13:39   #3
Zoon
Hunting Wabbits since the 80s
 
Zoon's Avatar
 
Join Date: Mar 2001
Location: Somewhere near a dremel in Bristol
Posts: 3,083
Zoon is the Cheesecake. Relix smiles down upon them.Zoon is the Cheesecake. Relix smiles down upon them.Zoon is the Cheesecake. Relix smiles down upon them.Zoon is the Cheesecake. Relix smiles down upon them.Zoon is the Cheesecake. Relix smiles down upon them.Zoon is the Cheesecake. Relix smiles down upon them.Zoon is the Cheesecake. Relix smiles down upon them.Zoon is the Cheesecake. Relix smiles down upon them.Zoon is the Cheesecake. Relix smiles down upon them.Zoon is the Cheesecake. Relix smiles down upon them.Zoon is the Cheesecake. Relix smiles down upon them.
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.
__________________
Datacentre architect and engineer extraordinaire. Maybe.
Zoon is offline   Reply With Quote
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +1. The time now is 11:18.
Powered by: vBulletin Version 3
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.