1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Open Source MYSQL Left / right join help

Discussion in 'Software' started by deathtaker27, 21 Nov 2012.

  1. deathtaker27

    deathtaker27 Modder

    Joined:
    17 Apr 2010
    Posts:
    2,238
    Likes Received:
    186
    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.
     
  2. Daedelus

    Daedelus What's a Dremel?

    Joined:
    7 May 2009
    Posts:
    253
    Likes Received:
    12
    What do you mean by "top 3"?
     
  3. Zoon

    Zoon Hunting Wabbits since the 80s

    Joined:
    12 Mar 2001
    Posts:
    5,869
    Likes Received:
    809
    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.
     

Share This Page