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 = " & tkeytkey 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] |
|
|
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 = " & tkeyThe specified field 'custID' could refer to more than one table listed in the FROM clause of your SQL statementDo 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. |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
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!! |
|
|
tahirjanjua
Starting Member
1 Post |
Posted - 2013-04-12 : 07:30:25
|
sup Ajarnjust need some help regarding some query .... emm would be very thankful if you can answer here is the querysql = "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 likesql = "select tb1.cid, tb2.cid, tb3.cid from tb1,tb2,tb3 where cid = '" & Request("ID") & "'"it saysThe specified field 'cid' could refer to more than one table listed in the FROM clause of your SQL statement.even tried tht as wellsql = "select tb1.cid as cid1, tb2.cid as cid2, tb3.cid as cid3 from tb1,tb2,tb3 where cid = '" & Request("ID") & "'"error saysThe specified field 'cid' could refer to more than one table listed in the FROM clause of your SQL statement./my/results.asp, line 18union 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") & "'"errorItem cannot be found in the collection corresponding to the requested name or ordinal./my/results.asp, line 22can 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 :) |
|
|
|