Hi, I have 4 tables and need to use 1 query for displaying : prodbarcode,prod_description,stock_on_hand,qty, (qty-stock_on_hand) Variance, cost_price, (Variance*cost_price) Var_Amount for a specific store in StCount.
StCount contains several sessionid for 1 store. C_Sess contains all the prodbarcode for each sessionid Then for the description, I need to use the table Prod and to match the qty with stock_on_hand, I need to use table Snap. VERY IMPORTANT: I need to have all prodbarcode in Snap as well as all prodbarcode in C_Sess. i.e. if a prodbarcode exist in Snap and does not exist in C_sess, the qty for that prodbarcode=0 , if a prodbarcode exist in C_Sess and does not exist in Snap, the Stock_on_hand for that prodbarcode=0 I'm using VB to do this with while ...wend loops and if... end ifs but it is taking me about 20 secs to display about 3400 items. As I am expecting to have more than 20,000 records soon, I need a query which will be much quicker! Please help. Table: Snap +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | prodbarcode | varchar(25) | | | | | | cost_price | double | | | 0 | | | stock_on_hand | float | YES | | NULL | | | store | varchar(15) | YES | | NULL | | +---------------+-------------+------+-----+---------+-------+ Table: C_Sess +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | sn | double | YES | MUL | NULL | | | sessionid | double | YES | MUL | NULL | | | prodbarcode | varchar(50) | YES | MUL | NULL | | | qty | double | YES | | NULL | | | cdate | date | YES | | NULL | | | ctime | varchar(12) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ Table: StCount +-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | sessionid | double | YES | MUL | NULL | | | cdate | date | YES | MUL | NULL | | | ctime | varchar(12) | YES | | NULL | | | userid | varchar(35) | YES | | NULL | | | store | varchar(25) | YES | MUL | NULL | | | team | varchar(255) | YES | | NULL | | | checkby | varchar(35) | YES | | NULL | | | chkdate | date | YES | | NULL | | | auditedby | varchar(35) | YES | | NULL | | | auddate | date | YES | | NULL | | | approveby | varchar(35) | YES | | NULL | | | appdate | date | YES | | NULL | | | remarks | varchar(50) | YES | | NULL | | +-----------+--------------+------+-----+---------+-------+ Table: Prod +------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | prod_code | varchar(15) | | PRI | | | | prodBarCode | varchar(25) | | MUL | | | | prod_description | varchar(50) | | | | | | prod_type | varchar(25) | | MUL | | | | prod_reference | varchar(35) | | MUL | | | +-----------+--------------+------+-----+---------+-------+ Regards, Velen
