Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 simple sql statement problem

Author  Topic 

Photoshopgod
Starting Member

10 Posts

Posted - 2004-07-01 : 11:23:53
HI,

I'm just learning sql and asp and have a simple question.

I have a database in access 2000.
I have two tables called table1 and table2 (I'll make this as simple as possible)
I have a page called page1.asp which views all the clients info.

table1 is the main table that will always have info in it. table2 has additional info that may not always have info related to the client. page1.asp needs to display info from both table1 and table2 regardless of whether or not there is information in table2. I have fields in both table1 and table2 that contains the custID for the client which are named custID.

Here is the simple sql statement I started with:

strsql = "SELECT * FROM table1, table2 WHERE custID = " & tkey

tkey is the custID brought from another form.

Obviously this generates an error. Any guidance would be much appreciated.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-07-01 : 11:38:05
[code]
SELECT * FROM table1 t1
left join table2 t2 on t2.custID = t1.custID
where custID = " & tkey
[/code]
Go to Top of Page

Photoshopgod
Starting Member

10 Posts

Posted - 2004-07-01 : 12:46:35
Hi RickD,

Thanks for the quick reply. I implemented your statement (below with actual names) and got the following error:

strsql = "SELECT * FROM tblCustomers tblCustomers left join tblParents tblParents on tblParents.custID = tblCustomers.custID WHERE custID = " & tkey


The specified field 'custID' could refer to more than one table listed in the FROM clause of your SQL statement

Do I need to lable the custID feild in the tblParents a different name? Or do I need to set up a relationship in the access database itself? Let me know if you see any errors with the sql statement above. Thanks again for the help.
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-01 : 13:46:03
Photo,

All that means is that in your WHERE clause, you need to prefix custID with the table name that you want it to reference, since custID appears in both tables. So, assuming that tblCustomers is the main table that always has data in it, you would change it to be:

WHERE tblCustomers.custID = " & tkey

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering
Go to Top of Page

Photoshopgod
Starting Member

10 Posts

Posted - 2004-07-01 : 14:13:54
Hi AjarnMark,

Boy, do I feel like an idiot?!?!?! I added the tblCustomers.custID to the sql statement and now get this error. This is the first time I've seen this error so I'm not sure exacally what this means. I'll search and try to find out, but any advice would be appreciated.


Microsoft JET Database Engine error '80004005'

Type mismatch in expression.

gpadb_couples_view.asp, line 44
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-01 : 17:48:32
Photo,

My two guesses would be these:

1) CustID in tblCustomers is text instead of numeric. In that case, you need to put single quotes around the value such as: WHERE tblCustomers.custID = '" & tkey & "'"

2) CustID in tblCustomers is not the same data type as custID in tblParents. Change one to make it match the other.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering
Go to Top of Page

Photoshopgod
Starting Member

10 Posts

Posted - 2004-07-01 : 19:18:40
Hi AjarnMark,

Thanks for the help. I forgot to make the custID in the tblParents a number field. I also had to rename the custID field in tblParents to pcustID. I appreciate all the help. I learned a hell of a lot today!!
Go to Top of Page

tahirjanjua
Starting Member

1 Post

Posted - 2013-04-12 : 07:30:25
sup Ajarn
just need some help regarding some query .... emm would be very thankful if you can answer here is the query
sql = "select tb1.cid from tb1 where cid = '" & Request("ID") & "'"
it works good but wht i want to add more table to get the desired result like
sql = "select tb1.cid, tb2.cid, tb3.cid from tb1,tb2,tb3 where cid = '" & Request("ID") & "'"
it says
The specified field 'cid' could refer to more than one table listed in the FROM clause of your SQL statement.

even tried tht as well

sql = "select tb1.cid as cid1, tb2.cid as cid2, tb3.cid as cid3 from tb1,tb2,tb3 where cid = '" & Request("ID") & "'"

error says
The specified field 'cid' could refer to more than one table listed in the FROM clause of your SQL statement.
/my/results.asp, line 18

union results are....
sql="select cid, 'tb1' as tableName from tb1 where cid = '" & Request("ID") & "' union select cid, 'tb2' as tableName from tb2 where cid = '" & Request("ID") & "' union select cid, 'tb3' as tableName from tb3 where cid = '" & Request("ID") & "'"

error
Item cannot be found in the collection corresponding to the requested name or ordinal.
/my/results.asp, line 22

can you tell me where i'm doing wrong ? i would wait for your answer thanks :) i read few of your comments n believe tht you can handle it just like the piece of cake :)
Go to Top of Page
   

- Advertisement -