Problem with JOIN on multiple columns

Discussion in 'Databases' started by SGBoise, Aug 26, 2009.

  1. #1
    Hi everyone,

    I'm having a lot problems trying to join tables. I hope some on smart can help me.

    I want to join two tables that have 2 columns on both tables to uniquely identify a record. Right now I'm using this command for the join.

    SELECT DISTINCT files.*
    FROM files
    JOIN transactions n1 ON n1.product_id = files.product_id
    JOIN transactions n2 ON n2.script_id = files.script_id
    WHERE transactions.order_number = '123456';
    
    Code (markup):
    It's still returning rows in the files table where the script_id doesn't my match product_id and script_id in the transactions table.

    For example:
    The product_id is 10 and script_id is 20 for order number 123456.

    It will return two records 2 records.
    Record 1:
    product_id = 10
    script_id = 20

    Record 2
    product_id = 10
    script_id = 21


    The product_id will match but script_id doesn't match. I think it's the way I have the join in my command.

    I tried left, right, inner joins and joining using the where clause but nothing seem to help. I tried kicking my computer and that didn't help either.

    I'm sure this is something simple but it's driving me crazy.
     
    SGBoise, Aug 26, 2009 IP
  2. premiumscripts

    premiumscripts Peon

    Messages:
    1,062
    Likes Received:
    48
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Try this:

    SELECT f.* FROM transactions t INNER 
    JOIN files f ON t.product_id = f.product_id AND t.script_id = f.script_id
    WHERE t.order_number = 123456;
    
    Code (markup):
    You may also experiment by changing INNER to LEFT or RIGHT, can't remember what the correct one is ;)
     
    premiumscripts, Aug 27, 2009 IP
    ceemage likes this.
  3. SGBoise

    SGBoise Peon

    Messages:
    647
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thanks. I'll try that.
     
    SGBoise, Aug 27, 2009 IP