A question of tables

[Laphan]

Hi All

As per my previous posting, I'm having trouble getting my schema out of my
DB so you will have to bear with me on this.

Basically I have the following tables (PLEASE NOTE: these are me
pseudo-trying to create DDL - apologies for errors):

CREATE TABLE STOCK
(STOCKID TEXT(30) NOT NULL PK,
CATID TEXT(30) NOT NULL,
MANUID TEXT(30) NOT NULL,
TAXID TEXT(30) NOT NULL,
PIC TEXT(50) NULL,
THUMBPIC TEXT(50) NULL,
UNIT TEXT(50) NULL,
WEIGHT INTEGER NULL,
QTYINSTOCK INTEGER
NEWSTOCK INTEGER
DISCON BYTE
SPECIAL BYTE
LASTUPDATE DATE/TIME);

CREATE TABLE STOCKDESCS
(STOCKID TEXT(30) NOT NULL,
STOCKNAME TEXT(50) NOT NULL,
SHORTDESC TEXT(255) NULL,
FULLDESC MEMO NULL,
LANGID TEXT(30) NOT NULL);

CREATE TABLE STOCKPRICES
(STOCKID TEXT(30) NOT NULL,
CURRENCYID TEXT(30) NOT NULL,
RRPNETAMOUNT DECIMAL(18,3) NULL,
RRPTAXAMOUNT DECIMAL(18,3) NULL,
NETAMOUNT DECIMAL(18,3) NULL,
TAXAMOUNT DECIMAL(18,3) NULL);

Not actually sure if I should have the above price amounts set to
DECIMAL(18,3), but I read that I should use the decimal option as it is more
accurate and the Access default is 18,3

Anyway this isn't the problem. My prob is that if I run the below query:

strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME, sd.SHORTDESC,
(sp.RRPNETAMOUNT + sp.RRPTAXAMOUNT) AS RRP, (sp.NETAMOUNT + sp.TAXAMOUNT) AS
PRICE FROM STOCK s, STOCKDESCS sd, STOCKPRICES sp WHERE s.STOCKID=sd.STOCKID
AND s.STOCKID=sp.STOCKID AND sd.LANGID='" & cSelLang & "' AND
sp.CURRENCYID='" & cSelCurrency & "' AND s.SPECIAL=1"

I get the cartisan (sp?) result that there has to be entries in the
STOCKDESCS and STOCKPRICES tables in order to bring back the records for the
selected lang and currency, ie cSelCurrency and cSelLang.

The reason for my query is that my ASP page has pop-up options for different
currencies and languages. The only problem is that the above query really
only works if the admin user has religiously entered descs and prices for
each currency and language.

I need it that if the prices and/or descs aren't entered then I still bring
back the rest of the data.

I'm putting in flags so that the user can omit the necessary langs and
currencies so missing data won't look odd.

Is it all to do with these outer joins again?

Your help would be appreciated.

Rgds

Laphan

[Laphan]

Just to elaborate on this theme, is there anyway that I could adapt the
query so that if there isn't a price available in the STOCKPRICES table for
the currencyid specified then I can change my query to take the exchange
rate from a 4th table (or session var) and convert the default currency
price (which should be in the STOCKPRICES table) with this exchange rate.

At present, I using the following method to cater for the above and
aforementioned problems (see below):

IF cSelCurrency <> cDefCurrency THEN

strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME, sd.SHORTDESC,
((sp.RRPNETAMOUNT * " & cSelCurrencyRate & ") + (sp.RRPTAXAMOUNT * " &
cSelCurrencyRate & ")) AS RRP, ((sp.NETAMOUNT * " & cSelCurrencyRate & ") +
(sp.TAXAMOUNT * " & cSelCurrencyRate & ")) AS PRICE FROM STOCK s, STOCKDESCS
sd, STOCKPRICES sp WHERE s.STOCKID=sd.STOCKID AND s.STOCKID=sp.STOCKID AND
sd.LANGID='" & cSelLang & "' AND s.SPECIAL=1"

ELSE

strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME, sd.SHORTDESC,
(sp.RRPNETAMOUNT + sp.RRPTAXAMOUNT) AS RRP, (sp.NETAMOUNT + sp.TAXAMOUNT) AS
PRICE FROM STOCK s, STOCKDESCS sd, STOCKPRICES sp WHERE s.STOCKID=sd.STOCKID
AND s.STOCKID=sp.STOCKID AND sd.LANGID='" & cSelLang & "' AND s.SPECIAL=1"

END IF

I then execute whichever query string is relevant.

Rgds

Laphan


Laphan <news@DoNotEmailMe.co.uk> wrote in message
news:ONxroEQMEHA.1388@TK2MSFTNGP09.phx.gbl...
Hi All

As per my previous posting, I'm having trouble getting my schema out of my
DB so you will have to bear with me on this.

Basically I have the following tables (PLEASE NOTE: these are me
pseudo-trying to create DDL - apologies for errors):

CREATE TABLE STOCK
(STOCKID TEXT(30) NOT NULL PK,
CATID TEXT(30) NOT NULL,
MANUID TEXT(30) NOT NULL,
TAXID TEXT(30) NOT NULL,
PIC TEXT(50) NULL,
THUMBPIC TEXT(50) NULL,
UNIT TEXT(50) NULL,
WEIGHT INTEGER NULL,
QTYINSTOCK INTEGER
NEWSTOCK INTEGER
DISCON BYTE
SPECIAL BYTE
LASTUPDATE DATE/TIME);

CREATE TABLE STOCKDESCS
(STOCKID TEXT(30) NOT NULL,
STOCKNAME TEXT(50) NOT NULL,
SHORTDESC TEXT(255) NULL,
FULLDESC MEMO NULL,
LANGID TEXT(30) NOT NULL);

CREATE TABLE STOCKPRICES
(STOCKID TEXT(30) NOT NULL,
CURRENCYID TEXT(30) NOT NULL,
RRPNETAMOUNT DECIMAL(18,3) NULL,
RRPTAXAMOUNT DECIMAL(18,3) NULL,
NETAMOUNT DECIMAL(18,3) NULL,
TAXAMOUNT DECIMAL(18,3) NULL);

Not actually sure if I should have the above price amounts set to
DECIMAL(18,3), but I read that I should use the decimal option as it is more
accurate and the Access default is 18,3

Anyway this isn't the problem. My prob is that if I run the below query:

strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME, sd.SHORTDESC,
(sp.RRPNETAMOUNT + sp.RRPTAXAMOUNT) AS RRP, (sp.NETAMOUNT + sp.TAXAMOUNT) AS
PRICE FROM STOCK s, STOCKDESCS sd, STOCKPRICES sp WHERE s.STOCKID=sd.STOCKID
AND s.STOCKID=sp.STOCKID AND sd.LANGID='" & cSelLang & "' AND
sp.CURRENCYID='" & cSelCurrency & "' AND s.SPECIAL=1"

I get the cartisan (sp?) result that there has to be entries in the
STOCKDESCS and STOCKPRICES tables in order to bring back the records for the
selected lang and currency, ie cSelCurrency and cSelLang.

The reason for my query is that my ASP page has pop-up options for different
currencies and languages. The only problem is that the above query really
only works if the admin user has religiously entered descs and prices for
each currency and language.

I need it that if the prices and/or descs aren't entered then I still bring
back the rest of the data.

I'm putting in flags so that the user can omit the necessary langs and
currencies so missing data won't look odd.

Is it all to do with these outer joins again?

Your help would be appreciated.

Rgds

Laphan