#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171

    Is this valid INNER JOIN?


    Hi;

    Is it legal to use OR in INNER JOIN's "ON"?
    SELECT cmt.customers.firstname,
    cmt.orders.reported_to_mwa,
    cmt.customers.phone,
    cmt.customers.postcode,
    cmt.customers.email,
    cmt.customers.id AS CRM_CID,
    cmt.orders.order_datetime,
    cmt.orders.id AS ORDER_ID,
    cmt.orders.amount_paid,
    cmt.orderproducts.product_id,
    cmt.orders.brand AS OBRAND
    FROM cmt.orders
    INNER JOIN cmt.orderproducts
    ON cmt.orders.id = cmt.orderproducts.order_id
    INNER JOIN htg.packages_daily_deal
    ON ( cmt.orderproducts.product_id =
    htg.packages_daily_deal.crm_id
    OR cmt.orderproducts.product_id =
    htg.packages_daily_deal.crm_id_thd
    )
    INNER JOIN cmt.customers
    ON cmt.orders.customer_id = cmt.customers.id
    WHERE ( cmt.orders.brand = 'thd'
    OR cmt.orders.brand = 'mwa' )
    AND cmt.orders.reported_to_mwa <> 1
    AND cmt.orders.status_id = 5
    ORDER BY email
    Thank you
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,007
    Rep Power
    9398
    Does it work and does it return the results you want?
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171
    Originally Posted by requinix
    Does it work and does it retsurn the results you want?
    Yes but it means nothing! ome features like group_concat or some cases of GROUP BY still work but only because it is mysql. I wanna make sure its valid everywhere.
    Thanks
  6. #4
  7. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,938
    Rep Power
    4033
    Originally Posted by English Breakfast Tea
    Is it legal to use OR in INNER JOIN's "ON"?Thank you
    Any conditional tests you can put in a WHERE clause can also be put into the ON clause of a join.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  8. #5
  9. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    You can write it that way, but the real question is what you have in the design of your database that causes you to do it.

    Because that query could potentially become very slow since OR's are not good for optimum index usage and placing an OR in a JOIN can cause a lot of index scans depending on the join direction.
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo