SQL need help constructing a statement

[Andrew Thomas]

i need to create a SQL statement for the following problem

Databse Layout

tbl_base
Base_Model - Case_Options
Celeron 1.4 - INW1,INW2,INW5
Celron 2.0 - INW5
Pentium 4 - INW1,INW5

tbl_cases
Case_Model - Code_Number
Micro ATX - INW1
Midi ATX - INW2
Tower Case - INW5

The above data forms two list boxes, 'bases' and 'cases', the data is selected in order by base first and case second.

I need a SQL statement which will only show appropriate cases that will fit the selected base unit.

eg

if a Celeron 2 is selected, only a Tower case will be available in the cases list box.

if a Pentium 4 is selected
cases to be shown in this list box will be Micro ATX and Tower case

hope this makes sense and any help much appreciated

Andy

[duckhunter]

If INW1,INW2,INW5 is the value of the field Case_Options, then you will not be able to join the two tables. Joining tables require relationships between tables usually represented by Primary/Foreign Key relationships. Looks like you need to architect the database a little more efficiently.

Define your Base Models in one table, then your case types in another. Finally a third table defines the one to many relationship that a Base has (Base has multiple options)

tbl_base: FieldName/Type
BaseID/Integer
Base_Model/VarChar

tbl_cases: FieldName/Type
CaseID/Integer
Case_Model/VarChar
Code_Number/VarChar

[new table]
tbl_Base_Options: FieldName/Type
OptionID/Integer
BaseID/Integer
CaseID/Integer

Now you can have a 1 to many relationship between tbl_base and available cases. Your select would look something like this.

SELECT
TB.BASEID, TB.BASE_MODEL, TC.CASEID, TC.CASE_MODEL, TC.CODE_NUMBER
FROM
TBL_BASE TB
JOIN
TBL_BASE_OPTIONS TBO
ON
TB.BASEID = TBO.BASEID
JOIN
TBL_CASES TC
ON
TBO.CASEID = TC.CASEID
WHERE
TB.Base_Model = <variable passed in>

Now a further step would be to have another field in TBL_BASE_OPTIONS called OptionTypeID that contains different option types (1=Case, 2=Memory, 3=Motherboard, etc.) Finally, when constructing your SELECT statement, try to use the BaseID (Integer) field instead of the Base_Model text field, it will run faster.

[Andrew Thomas]

thanks for helping?

ive created the following SQL:

rs_case.Source = "SELECT PC_Base.pc_base_Code_Number, PC_Base.pc_base_model, PC_Cases.pc_case_Code_Number, PC_Cases.pc_case_model FROM ((PC_base INNER JOIN base_case_link ON PC_Base.pc_base_Code_Number = base_case_link.pc_base_code_number) INNER JOIN Pc_cases ON base_case_link.pc_case_code_number = pc_cases.pc_case_code_number) WHERE pc_base_model = '" & Request.QueryString("pc_base_model") & "' ;"

but get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

can anyone see whats wrong? Im using an access database.

thanks

[Staffa]

It's always good to DIM your variable outside your query string, ie

DIM whatever
whatever = Request.QueryString("pc_base_model")

and insert :

WHERE pc_base_model = '" & whatever & "' ;"

[Andrew Thomas]

Thanks for the DIM suggestion.

Ive looked at an example and now have the following:

base_model_result = Request.QueryString("pc_base_model")
Dim strSQL

strSQL = "SELECT PC_Base.pc_base_Code_Number, PC_Base.pc_base_model, PC_Cases.pc_case_Code_Number, PC_Cases.pc_case_model FROM PC_base INNER JOIN ( base_case_link INNER JOIN PC_Cases ON base_case_link.pc_case_code_number = pc_cases.pc_case_code_number) ON PC_Base.pc_base_Code_Number = base_case_link.pc_base_code_number WHERE pc_base_model = '" & base_model_result & "' ;"

base_model_result does display the chosen record

but i still get the same error? is the error in SQL or could it be the database?

[defanjos]

It might work if you use LIKE instead of =, for example:
pc_base_model LIKE '" & Request.QueryString("pc_base_model") & "'

I think using "LIKE" is slower than "=", so, even if it works, you might have to find a different way to do it.

[Staffa]

Unless you have your first DIM not shown in your last post, you should have :
----------
Dim strSQL,base_model_result

base_model_result = Request.QueryString("pc_base_model")

strSQL = "SELECT PC_Base.pc_base_Code_Number, .....
------------
Do not include a space after the comma in Dim

Also, is your pc_base_model a string or a number in your database.

From the number of quotes in your sql
.........WHERE pc_base_model = '" & base_model_result & "' ;"
it should be a string, if it's a number then remove the single quotes
Keep trying this is a common error which usually depends on something very simple and easily overlooked.

[duckhunter]

The SQL I wrote above is for SQL Server, not Access. Use the Query builder in Access to build the query, then click View Query and you can copy/paste it.

The join syntax for Access is different than SQL Server.

[Andrew Thomas]

thanks for all your help, i have solved the problem now, the code is below

rs_case.Source = "SELECT pc_base.pc_base_code_number, pc_base.pc_base_model, pc_cases.pc_case_code_number, pc_cases.pc_case_model, pc_base.pc_base_price, pc_cases.pc_case_price FROM pc_base INNER JOIN ( base_case_link INNER JOIN pc_cases ON base_case_link.pc_case_code_number = pc_cases.pc_case_code_number) ON pc_base.pc_base_code_number = base_case_link.pc_base_code_number WHERE pc_base_model = '" & base_model_result & "' ;"

thanks