SEO

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