Can you Inner Join Expressions ?

[TonyF]

Hi,

I am trying to get a join between two tables (stockm and wdorderm)
by using an inner join on two expressions I have created in the query,
but I keep getting a message stating join expression not supported.

Is it possible to create joins using expreesions ?

This is the sql of the query,

SELECT scheme_wdorderm.card_code, scheme_wdorderm.job_desc, scheme_wdorderm.box_unit_desc, scheme_wdorderm.box_label_stock, scheme_wdorderm.pack_label_stock, scheme_wdorderm.caption_id, scheme_wdorderm.caption, scheme_wdorderm.pack_for_collation, scheme_wdorderm.new_prod, scheme_wdorderm.closure, scheme_wdorderm.works_order_1, scheme_wdorderm.works_order_2, scheme_wdorderm.works_order_3, scheme_wdorderm.works_order_4, scheme_wdorderm.env_stock_1, scheme_wdorderm.env_stock_2, scheme_wdorderm.env_stock_3, scheme_wdorderm.env_stock_4, scheme_wdorderm.env_desc_1, scheme_wdorderm.env_desc_2, scheme_wdorderm.env_desc_3, scheme_wdorderm.env_desc_4, scheme_wdorderm.card_ref_1, scheme_wdorderm.card_ref_2, scheme_wdorderm.card_ref_3, scheme_wdorderm.card_ref_4, scheme_wdorderm.env_page, scheme_wdorderm.box_stock, scheme_wdorderm.front_lab_stock, scheme_wdorderm.rear_lab_stock, scheme_wdorderm.fold_lab_stock, scheme_wdorderm.special_lab_stock, scheme_wdorderm.price_lab_stock, scheme_wdorderm.barcode_lab_stock, scheme_wdorderm.barcode, scheme_wdorderm.flap_closure, scheme_wdorderm.tape_stock, scheme_wdorderm.scoreline_opt, scheme_wdorderm.wrap_opt, scheme_wdorderm.film_inner_size, scheme_wdorderm.film_inner_stock, scheme_wdorderm.film_outer_opt, scheme_wdorderm.film_outer_size, scheme_wdorderm.film_outer_stock, scheme_wdorderm.sundries_type, scheme_wdorderm.sundries_stock, scheme_wdorderm.spec_sheet, scheme_wdorderm.card_prefix, scheme_wdorderm.envelope_type_1, scheme_wdorderm.envelope_type_2, scheme_wdorderm.envelope_type_3, scheme_wdorderm.envelope_type_4, scheme_wdorderm.free_text_1, scheme_wdorderm.free_text_2, scheme_wdorderm.free_text_3, scheme_wdorderm.free_text_4, scheme_wdorderm.design_count, scheme_wdorderm.pack_unit, scheme_wdorderm.box_unit, scheme_wdorderm.box_weight, scheme_wdorderm.collation_qty, scheme_wdorderm.card_sell_price, scheme_wdorderm.env_sell_price, scheme_wdorderm.box_sell_price, scheme_wdorderm.lab_sell_price, scheme_wdorderm.sundries_sell_pric, scheme_wdorderm.total_sell_price, scheme_wdorderm.card_cost, scheme_wdorderm.env_cost, scheme_wdorderm.box_cost, scheme_wdorderm.lab_cost, scheme_wdorderm.sundries_cost, scheme_wdorderm.total_cost, scheme_wdorderm.labour_value, scheme_wdorderm.overhead_value, scheme_wdorderm.qty_per_box, scheme_wdorderm.pack_weight, scheme_wdorderm.pack_depth, scheme_wdorderm.box_qty_reqd, scheme_wdorderm.env_price_1, scheme_wdorderm.env_price_2, scheme_wdorderm.env_price_3, scheme_wdorderm.env_price_4, scheme_wdorderm.box_price, scheme_wdorderm.tape_qty_m, scheme_wdorderm.film_inner_form, scheme_wdorderm.film_inner_waste, scheme_wdorderm.film_inner_qty, scheme_wdorderm.film_outer_form, scheme_wdorderm.film_outer_waste, scheme_wdorderm.film_outer_qty, scheme_wdorderm.price, scheme_wdorderm.labour, scheme_wdorderm.overhead, scheme_wdorderm.env_length_1, scheme_wdorderm.env_length_2, scheme_wdorderm.env_length_3, scheme_wdorderm.env_length_4, scheme_wdorderm.env_width_1, scheme_wdorderm.env_width_2, scheme_wdorderm.env_width_3, scheme_wdorderm.env_width_4, RTrim([works_order_1]) & "-1-" & ([card_ref_1]) AS CARD_STOCK_1, RTrim([works_order_2]) & "-1-" & ([card_ref_2]) AS CARD_STOCK_2, RTrim([works_order_3]) & "-1-" & ([card_ref_3]) AS CARD_STOCK_3, RTrim([works_order_4]) & "-1-" & ([card_ref_4]) AS CARD_STOCK_4, Mid([film_inner_stock],1,2) AS CR_WH, scheme_stockm.description, scheme_stockm.long_description
FROM scheme_wdorderm
INNER JOIN scheme_stockm ON CR_WH=scheme_stockm.warehouse & CARD_STOCK_1=scheme_stockm.product

The top portion is just selecting everything from the table wdorderm table
and two fields from the stockm table, it seems to be the bottom part trying to join on the two expressions CR_WH and CARD_STOCK_1 that it doesn't like.

I have attached a screenshot of the error message

any help pointing me in the right direction would be greatly appreciated,

 

[pbaldy]

Generally speaking you can join on an expression, but that expression would need to be in the join. You can't use the alias.

 

[TonyF]

Hi Paul,

Thanks for the reply,
I have tried using the full expression name but I still get the same message, it then highlights this portion of the join when I click ok

Mid([film_inner_stock],1,2)=scheme_stockm.warehouse & RTrim([works_order_1]) & "-1-" & ([card_ref_1])=scheme_stockm.product

I cannot see what is wrong with this though is the syntax ok ?
But then again it would give me a syntax error i guess

Cheers
Tony

 

[vbaInet]

Create a new query based on the query with the Expression in it. From that query you will be able to join on that field.

 

[Banana]

TonyF, I don't think you can have an expression with two equal sign. If you want to require that 3 columns be equal, then I think you have to do:

A = B AND A = C

instead of

A = B = C

 

[TonyF]

Many thanks vbaInet that has worked perfectly,
I have officially thanked you.
Cheers
Tony


Banana I am not sure what you mean, I need to join on two columns from one table to two columns in the other table but creating a new query based on the other query has worked, anyway thanks for the reply

Regards
Tony