SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 MS Access
 simple sql statement problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Photoshopgod
Starting Member

10 Posts

Posted - 07/01/2004 :  11:23:53  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 07/01/2004 :  11:38:05  Show Profile  Reply with Quote

SELECT * FROM table1 t1 
left join table2 t2 on t2.custID = t1.custID 
where custID = " & tkey
Go to Top of Page

Photoshopgod
Starting Member

10 Posts

Posted - 07/01/2004 :  12:46:35  Show Profile  Reply with Quote
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

USA
3246 Posts

Posted - 07/01/2004 :  13:46:03  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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

Edited by - AjarnMark on 07/01/2004 13:46:50
Go to Top of Page

Photoshopgod
Starting Member

10 Posts

Posted - 07/01/2004 :  14:13:54  Show Profile  Reply with Quote
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

USA
3246 Posts

Posted - 07/01/2004 :  17:48:32  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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

Edited by - AjarnMark on 07/01/2004 17:49:10
Go to Top of Page

Photoshopgod
Starting Member

10 Posts

Posted - 07/01/2004 :  19:18:40  Show Profile  Reply with Quote
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 Posts

Posted - 04/12/2013 :  07:30:25  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000