Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    21

    Unanswered: Informix 9.3 - Not Equal Join

    I have an = join that works just fine.

    SELECT tp_promo1.sl_store, tp_promo1.total_pos_price_before_discount, tp_promo1.total_pos_price_after_discount, tp_promo1.total_discount_amount, tp_promo1.total_discounts_redeemed, tp_promo3.qualifying_items_sold
    FROM tp_promo1, tp_promo3
    WHERE tp_promo1.sl_store = tp_promo3.sl_store


    I also want to know the records that are not included in the above query, so I'm trying to execute a NOT EQUAL/NOT IN join using...

    SELECT tp_promo1.sl_store, tp_promo1.total_pos_price_before_discount, tp_promo1.total_pos_price_after_discount, tp_promo1.total_discount_amount, tp_promo1.total_discounts_redeemed, tp_promo3.qualifying_items_sold
    FROM tp_promo1, tp_promo3
    WHERE tp_promo3.sl_store NOT IN (SELECT * FROM tp_promo1, tp_promo3 WHERE tp_promo1.sl_store = tp_promo3.sl_store)


    ...but I'm receiving

    "A subquery has returned not exactly one column."

    I've also tried...

    WHERE tp_promo1.sl_store <> tp_promo3.sl_store

    ...but I'm receiving about 19,000 rows when I should only be receiving 9 rows.

    TP_PROMO1 contains 136 rows. TP_PROMO3 contains 145 rows. I want to know what 9 rows aren't being select from TP_PROMO3.

    Any help would be appreciated!

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi, in Informix dialect (and ANSI SQL I believe) the construction
    Code:
    WHERE tp_promo3.sl_store NOT IN 
    (SELECT * FROM tp_promo1, tp_promo3 WHERE tp_promo1.sl_store = tp_promo3.sl_store)
    is not valid because the (NOT) IN operator works on simple operands only. So you should use
    Code:
    FROM tp_promo1
    WHERE sl_store NOT IN (SELECT sl_store FROM tp_promo3)
    instead. Another approach is to use a correlated subquery like
    Code:
    FROM tp_promo1
    WHERE NOT EXISTS
    (SELECT * FROM tp_promo3 WHERE tp_promo1.sl_store = tp_promo3.sl_store)
    but the first solution probably performs better.

    The 19000 rows you're mentioning are a result of the cartesian product of the join without a discrete join clause of
    Code:
    FROM tp_promo1, tp_promo3
    WHERE tp_promo1.sl_store <> tp_promo3.sl_store
    This way you'll get a resultset of approximately 139 * (145 -9) records.

    Regards,
    Hans

  3. #3
    Join Date
    May 2008
    Posts
    21
    Hans,

    I apologize for not thanking you earlier. Your explainations and solutions were great!

    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •