SQL Select

[jeremywatco]

I want to do a select statement that select values from one table based on the values from another table.

Example: SELECT data FROM data_table WHERE datatable2.fieldname = 'blah'

Can I use "dot" notation?

Any ideas?

[BigDaddy]

3 options I can think of:

1. Join 2 tables on a common value

SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID

http://www.w3schools.com/sql/sql_join.asp


2. Do a nested query:

SELECT *
FROM table
WHERE (field =(SELECT field FROM table WHERE field = ' ' ))

This is where you'll only get one record back from the query that is nested inside the other one. The one that's nested will execute first, then return a value to the other query. It only works if you get one record from the nested query.

3. Do a query to get your value from one table, then do a second query to get your record using what you got from the first

SELECT field from table where field = ""
SELECT field from table where field = "**value from above query**

[jeremywatco]

Thanks. I'll Give it a try.

I am having another issue though. I keep getting an error when doing my select.

Here is my select:

sqlString = "SELECT group_id FROM user WHERE username=user13"
Set RS = Con.Execute( sqlString )

Now user13 is a variable that is set earlier. The error message is that "too few parameters, expected 1" do i have to do something different in a select when comparing it to a variable?

[BigDaddy]

Do me a favor. So we can see what your actual select statement is, put

response.write sqlstring : response.end

right after you set the value of the sqlstring and before the

set RS =

line. That will print out what is actually being submitted to the database.


PS...did you mean to write "con.execute"? I'm thinking it might need to be "conn.execute"

[jeremywatco]

Here it is:

SELECT group_id FROM user WHERE username=user13


Also:

I have my connection defined as con

[jeremywatco]

Actually I figured it out.

It needed to be:

SELECT group_id FROM users WHERE username='" & user12 & "'"

Thanks for the help!

[BigDaddy]

Cool. I was thinking it was probably the syntax of the select statement.

[whammy]

Just remember, with text-type database fields:

'" & variable & "'

with int or other numeric type fields (bit, etc.):

" & variable & "

Although SQL Server is forgiving in this respect in some cases, it's a good practice. I usually write all my SQL code in SQL view when interacting with a database, so I'm used to when not to use single quotes, etc.

:)