SQL Query... Confused!

[weee]

SQL = "SELECT service1,service2,service3 FROM serivces_TABLE WHERE county='" & county & "' AND state='" & state & "'"

I'm running an SQL query that select a number from the different type of services I have. The number represent the id number of the company.

As you can see in the SQL query will select the service while I compate the county and the state.

My question is How do I show the name of the company insted of the ID?
The company details in a different DB (companies_TABLE) under "name".

Thanks!

[DanWalter]

SQL = "SELECT service1,service2,service3 FROM serivces_TABLE WHERE county='" & county & "' AND state='" & state & "'"

I'm running an SQL query that select a number from the different type of services I have. The number represent the id number of the company.

As you can see in the SQL query will select the service while I compate the county and the state.

My question is How do I show the name of the company insted of the ID?
The company details in a different DB (companies_TABLE) under "name".

Thanks!

You need to join the tables:
Select company_name, services_table.service1, companies_table.id from services_table JOIN COMPANIES_TABLE ON SERVICES_TABLE.SERVICE1 = COMPANIES_TABLE.ID WHERE....

This assumes the common key between the tables is service1 and id.

Dan

[weee]

Here's what I got so far:


SQL = "SELECT a.companyName, a.id, s.WDR, s.MR, s.FDRS, s.SC FROM servicesTBL JOIN companiesTBL ON servicesTBL.WDR WHERE county='" & county & "' AND state='" & state & "'"


It seems like I'm missing something. I'm not sure what though.
What can it be?

WDR,MR,FDRS,SC = the types of services.

Error:
Microsoft JET Database Engine error '80040e14'

Syntax error in FROM clause.

[russell]

the error is in your join on clause. You need to join on TableA.field = TableB.field. And your aliases are messed up

try this, changing the field names if i got 'em wrong

SQL = "SELECT c.companyName, c.id, s.WDR, s.MR, s.FDRS, s.SC " &_
"FROM servicesTBL s" &_
"JOIN companiesTBL c" &_
"ON s.companyId = c.id " &_
"WHERE county='" & county & "' " &_
"AND state='" & state & "'"

[weee]

Same error as before:


SQL = "SELECT c.companyName, c.id, s.WDR, s.MR, s.FDRS, s.SC FROM servicesTBL s JOIN companiesTBL c ON s.companyId = c.id WHERE county='" & county & "' AND stateName='" & stateName & "'"


What can it be?

[russell]

Weee, I can't remember, are using Access or MSSQL? If it's Access, change "JOIN" to "INNER JOIN"

also do a response.write sql to see what the query is producing...

[weee]

I'm using Access. added the Inner join but now the error is different:
Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

[russell]

either county or statename is empty. do a response.write sql to see the query that you're passing to the db.

this is always the first thing to check when debugging

[weee]

I know, I thought so too but they are not. I already checked it out.
That's not the problem...

[russell]

what happens if you response.write sql?

[weee]

SELECT a.companyName, a.id, s.WDR, s.MR, s.FDRS, s.SC FROM servicesTBL s INNER JOIN associatesTBL a ON s.companyId = a.id WHERE county='Los Angeles' AND stateName='California'

[russell]

and if you paste that into a query window in access what then?