Informix 9.3 - Not Equal Join

skisalomon77 · #1 (**permalink**) 10-13-08, 12:00

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!

Tyveleyn · #2 (**permalink**) 10-14-08, 15:58

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

skisalomon77 · #3 (**permalink**) 06-18-09, 11:54

Hans,

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

Thank you!

Internet Services

Web Development

Digital Marketing

Consumer Tech